Which SQL query is better, MATCH AGAINST or LIKE?

Question

To search the database for rows that have both keywords "foo" AND "bar" in any of the columns "foo_desc" and "bar_desc", I would do something like:

SELECT * 
FROM t1 
WHERE MATCH (t1.foo_desc, t2.bar_desc) AGAINST ('+foo* +bar*' IN BOOLEAN MODE)

or

SELECT * 
FROM t1 
WHERE (CONCAT(t1.foo_desc, t2.bar_desc) LIKE '%foo%') AND (CONCAT(t1.foo_desc, t2.bar_desc) LIKE '%bar%')

I expect the downside of the last query is performance.

The upside is that the LIKE query finds 'xxfoo' where MATCH AGAINST does not.

Which is the preferred one or is there a better solution?

Answer 1

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.