Which SQL query is better, MATCH AGAINST or LIKE?

Update

As of MySQL 5.6 and later, InnoDB tables supports Match... Against.

The first is much better. On MyISAM tables it will use a full text index against those columns. The other will do a full table scan doing a concat on every row and then a comparison.

LIKE is only efficient if you're doing it against:

• a column (not a result of a function unless your particular database vendor supports functional indexes--Oracle, for example--and you're using them);
• the start of the column (ie LIKE 'blah%' as opposed to LIKE '%blah%'); and
• a column that's indexed.

If any one of those conditions are not true the only way for the SQL engine to execute the query is by doing a full table scan. This can be usable under about 10-20 thousand rows. Beyond that it quickly becomes unusable however.

Note: One problem with MATCH on MySQL is that it seems to only match against whole words so a search for 'bla' won't match a column with a value of 'blah', but a search for 'bla*' will.