MySQL LIKE vs LOCATE

Does anyone know which o开发者_JAVA技巧ne is faster:

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

or

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

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)

+1 to @Mchl for answering the question most directly.

But we should keep in mind that neither of the solutions can use an index, so they're bound to do table-scans.

Trying to decide between a cloth or plastic adhesive bandage is kind of silly, when you're trying to patch the hull of the Titanic.

For this type of query, one needs a full-text search index. Depending on the size of the table, this will be hundreds or thousands of times faster.

I did some tests as Mchi did.And I think it's hard to say which one is faster. It looks like depending on the first occurrence of the substring.

mysql> select benchmark(100000000, 'afoobar' like '%foo%');
+----------------------------------------------+
| benchmark(100000000, 'afoobar' like '%foo%') |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
1 row in set (9.80 sec)

mysql> select benchmark(100000000, locate('foo', 'afoobar'));
+------------------------------------------------+
| benchmark(100000000, locate('foo', 'afoobar')) |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (8.08 sec)

mysql> select benchmark(100000000, 'abfoobar' like '%foo%');
+-----------------------------------------------+
| benchmark(100000000, 'abfoobar' like '%foo%') |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+
1 row in set (10.55 sec)

mysql> select benchmark(100000000, locate('foo', 'abfoobar'));
+-------------------------------------------------+
| benchmark(100000000, locate('foo', 'abfoobar')) |
+-------------------------------------------------+
|                                               0 |
+-------------------------------------------------+
1 row in set (10.63 sec)

mysql> select benchmark(100000000, 'abcfoobar' like '%foo%');
+------------------------------------------------+
| benchmark(100000000, 'abcfoobar' like '%foo%') |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (11.54 sec)

mysql> select benchmark(100000000, locate('foo', 'abcfoobar'));
+--------------------------------------------------+
| benchmark(100000000, locate('foo', 'abcfoobar')) |
+--------------------------------------------------+
|                                                0 |
+--------------------------------------------------+
1 row in set (12.48 sec)

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.5.27-log |
+------------+
1 row in set (0.01 sec)