哪个更快——INSTR 还是 LIKE?

Which is faster — INSTR or LIKE?(哪个更快——INSTR 还是 LIKE?)

本文介绍了哪个更快——INSTR 还是 LIKE?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如果您的目标是测试某个字符串是否存在于 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

对比

columnname LIKE '%mystring%'

推荐答案

FULLTEXT 搜索绝对会更快,正如 kibibu 在上面的评论中指出的那样.

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?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本文标题为:哪个更快——INSTR 还是 LIKE?

基础教程推荐