使用“like"优化 MySQL 搜索和通配符
如何查询像
SELECT * FROM sometable WHERE somefield LIKE '%value%'
要优化吗?
这里的主要问题是阻止 DBMS 使用索引的第一个通配符.
The main issue here is the first wildcard which prevents DBMS from using index.
此外，somefield 值是纯字符串(不是一段文本)，因此无法执行全文搜索.
What is more, somefield value is solid string (not a piece of text) so fulltext search could not be performed.
推荐答案
两种方式:
(1) 使用内存表，所以速度非常快.
(1) use an in-memory table so it goes very fast.
(2) 编写比 foo LIKE '%bar%' 更好的索引和搜索算法.如果不了解您的问题，就无法就此提出任何建议.
(2) cook up a better index and search algorithm than foo LIKE '%bar%'. It's not possible to make any suggestions about this without knowing more about your problem.
正如您所指出的，%bar% 模式保证每次查找都进行表扫描，这使数据库软件中任何可能的搜索独创性无效.
As you have pointed out, the %bar% pattern guarantees a table-scan for every lookup, which nullifies any possible search ingenuity in the database software.
相关文章