## MySQL LIKE vs LOCATE: Which is Faster for Pattern Matching?

MySQL LIKE vs LOCATE: Which is More Efficient?

When performing pattern matching queries in MySQL, you have two main options: the LIKE operator and the LOCATE function. Which one performs faster?

To answer this question, let's compare the performance of these two approaches using a simple benchmark. The following query uses the LIKE operator to find rows where a column contains a specific text:

SELECT * FROM table WHERE column LIKE '%text%';

The following query uses the LOCATE function to perform the same operation:

SELECT * FROM table WHERE LOCATE('text',column)>0;

Running these queries against a large dataset shows that the LIKE operator is marginally faster, primarily because it avoids the additional comparison (> 0) required by LOCATE. Here are the results from a benchmark:

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)

The above is the detailed content of ## MySQL LIKE vs LOCATE: Which is Faster for Pattern Matching?. For more information, please follow other related articles on the PHP Chinese website!