问题描述
如果您的目标是测试MySQL列中是否存在字符串(类型为'varchar','text','blob'等),那么以下哪一项是更快/更有效/更好地使用,以及为什么?
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%'
正如kibibu在上述评论中指出的那样,
推荐答案
FULLTEXT搜索绝对会更快.
FULLTEXT searches are absolutely going to be faster, as kibibu noted in the comments above.
但是:
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)
在我的测试中,它们的表现完全相同.它们都不区分大小写,并且通常会执行全表扫描,这在处理高性能MySQL时通常是禁止的.
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)
在这种情况下,仅带有后缀通配符的LIKE要快得多.
In which case, the LIKE with only a suffix wildcard is much faster.
这篇关于哪个更快-INSTR还是LIKE?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!