Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Which SQL query is better, MATCH AGAINST or LIKE?

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?

like image 727
Wil Avatar asked Apr 27 '09 09:04

Wil


People also ask

Which is faster like or in SQL?

1 Answer. Using '=' operator is faster than the LIKE operator in comparing strings because '=' operator compares the entire string but the LIKE keyword compares by each character of the string. We can use LIKE to check a particular pattern like column values starting with 'abc' in this case.

When should I use like or SQL?

The SQL LIKE Operator The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.

What can I use instead of like in SQL?

You may come across the situation, where you need alternate to “like” keyword of SQL, that is to search for sub-string in columns of the table. The one way to achieve it to use instr() function, instr() function takes 3 parameters in account .


1 Answers

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.

like image 156
cletus Avatar answered Sep 20 '22 10:09

cletus