Menu
Does anyone know which one is faster:
SELECT * FROM table WHERE column LIKE '%text%'; or
SELECT * FROM table WHERE LOCATE('text',column)>0; 
898
LOCATE() function in MySQL is used for finding the location of a substring in a string. It will return the location of the first occurrence of the substring in the string. If the substring is not present in the string then it will return 0.
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.
the LIKE comparison checks the leading edge of your column, and is done. INSTR searches for the string anywhere in the column. so it makes sense that LIKE would be faster in this case - it searches less characters.
LOCATE() function MySQL LOCATE() returns the position of the first occurrence of a string within a string. Both of these strings are passed as arguments. An optional argument may be used to specify from which position of the string (i.e. string to be searched) searching will start.
Added April 20th, 2015: Please read also Hallie's answer below
First one but marginally. Mostly because it doesn't have to do an extra > 0 comparison.
mysql> SELECT BENCHMARK(100000000,LOCATE('foo','foobar')); +---------------------------------------------+ | BENCHMARK(100000000,LOCATE('foo','foobar')) | +---------------------------------------------+ | 0 | +---------------------------------------------+ 1 row in set (3.24 sec) mysql> SELECT BENCHMARK(100000000,LOCATE('foo','foobar') > 0); +-------------------------------------------------+ | BENCHMARK(100000000,LOCATE('foo','foobar') > 0) | +-------------------------------------------------+ | 0 | +-------------------------------------------------+ 1 row in set (4.63 sec) mysql> SELECT BENCHMARK(100000000,'foobar' LIKE '%foo%'); +--------------------------------------------+ | BENCHMARK(100000000,'foobar' LIKE '%foo%') | +--------------------------------------------+ | 0 | +--------------------------------------------+ 1 row in set (4.28 sec) mysql> SELECT @@version; +----------------------+ | @@version | +----------------------+ | 5.1.36-community-log | +----------------------+ 1 row in set (0.01 sec) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
