Which is faster — INSTR or LIKE?

Question

If your goal is to test if a string exists in a MySQL column (of type 'varchar', 'text', 'blob', etc) which of the following is faster / more efficient / better to use, and why?

Or, is there some other method that tops either of these?

INSTR( columnname, 'mystring' ) > 0 

vs

columnname LIKE '%mystring%' 

Answer 1

FULLTEXT searches are absolutely going to be faster, as kibibu noted in the comments above.

However:

mysql> select COUNT(ID) FROM table WHERE INSTR(Name,'search') > 0; +-----------+ | COUNT(ID) | +-----------+ |     40735 |  +-----------+ 1 row in set (5.54 sec)  mysql> select COUNT(ID) FROM table WHERE Name LIKE '%search%'; +-----------+ | COUNT(ID) | +-----------+ |     40735 |  +-----------+ 1 row in set (5.54 sec) 

In my tests, they perform exactly the same. They are both case-insensitive, and generally they perform full-table scans, a general no-no when dealing with high-performance MySQL.

Unless you are doing a prefix search on an indexed column:

mysql> select COUNT(ID) FROM table WHERE Name LIKE 'search%'; +-----------+ | COUNT(ID) | +-----------+ |         7 |  +-----------+ 1 row in set (3.88 sec) 

In which case, the LIKE with only a suffix wildcard is much faster.

Answer 2

MySQL - INSTR vs LOCATE vs LIKE vs REGEXP

For me the INSTR and LOCATE performed the fastest:

# 5.074 sec SELECT BENCHMARK(100000000,INSTR('foobar','foo'));  # 5.086 sec SELECT BENCHMARK(100000000,LOCATE('foo','foobar'));   # 8.990 sec SELECT BENCHMARK(100000000,'foobar' LIKE '%foo%');  # 14.433 sec SELECT BENCHMARK(100000000,'foobar' REGEXP 'foo');   # 5.5.35-0ubuntu0.12.10.2  SELECT @@version;