MySQL LIKE vs LOCATE Performance Comparison

When searching for data in MySQL, you may wonder which operator is more efficient: LIKE or LOCATE? This article explores the performance differences between these two operators.

In a typical usage scenario, LIKE is slightly faster than LOCATE. This is primarily due to the fact that LIKE does not perform the additional comparison against 0 that LOCATE does.

As illustrated by the benchmark results below, LIKE consistently performs marginally better than LOCATE for a large number of iterations:

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)

It's important to note that these results may vary depending on the specific database version and the size and contents of the table you're searching. In general, however, LIKE is considered to be the more efficient operator for wildcard searches.