29

Does anyone know which one is faster:

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

or

SELECT * FROM table WHERE LOCATE('text',column)>0;
Ghooti Farangi
  • 19,926
  • 15
  • 46
  • 61

3 Answers3

29

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)
Community
  • 1
  • 1
Mchl
  • 61,444
  • 9
  • 118
  • 120
  • How does `benchmark` works, What's the accuracy of these numbers? – Pacerier Apr 16 '15 at 04:03
  • `BENCHAMRK` is simply running the provided expression a given number of times. https://dev.mysql.com/doc/refman/5.5/en/select-benchmarking.html As for accuracy I can't really tell you much. I found it to be pretty good when it comes to precision though. – Mchl Apr 20 '15 at 13:50
6

+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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 2
    The problem with full-text in MySQL is that it is not currently capable of wildcard search, only prefix search (`foo*` will match `foobar`, but `*bar` won't). – Itai Apr 19 '16 at 22:37
  • 1
    Apache Lucene and Solr have the same limitation. Sphinx Search supports infix indexing, so you can put wildcards at the start of a pattern. See http://sphinxsearch.com/docs/current/conf-min-infix-len.html – Bill Karwin Apr 20 '16 at 06:46
4

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)
Hallie
  • 41
  • 2