## Which is Faster in MySQL: `LIKE` or `LOCATE` for String Matching?

Which One Is Faster: MySQL LIKE vs LOCATE?

In MySQL, there are two commonly used functions for pattern matching in strings: LIKE and LOCATE. While both functions can be used to search for substrings within a string, they differ in their approach and performance characteristics.

LIKE vs LOCATE

The LIKE operator uses wildcards to match patterns of characters. It supports various wildcard characters, including % (matches any number of characters) and _ (matches a single character). For example:

<code class="sql">SELECT * FROM table WHERE column LIKE '%text%';</code>

This query searches for all rows where the column value contains the string "text" anywhere within it.

On the other hand, the LOCATE function searches for the position of a specified substring within a string. It returns the index of the first occurrence of the substring, or 0 if the substring is not found. For example:

<code class="sql">SELECT * FROM table WHERE LOCATE('text', column) > 0;</code>

This query searches for all rows where the column value contains the substring "text" anywhere within it.

Performance Comparison

In general, the LIKE operator is marginally faster than LOCATE. This is because LIKE does not have to perform an additional comparison to check if the result is greater than 0.

To demonstrate this difference in performance, consider the following benchmarks:

<code class="sql">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)</code>

As the benchmark results show, LIKE is marginally faster than LOCATE when searching for a substring within a string. However, the difference in performance is relatively small for most practical use cases.

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