使用MySQL中的LIKE子句优化表搜索 [英] optimize tables for search using LIKE clause in MySQL

问题描述

我正在为我的网站的消息部分构建一个搜索功能，并且有一个超过9,000,000行的消息数据库，以及 sender 上的索引， subject 和消息字段。我希望在我的查询中使用LIKE mysql子句，例如（ex）

I am building a search feature for the messages part of my site, and have a messages database with a little over 9,000,000 rows, and and index on the sender, subject, and message fields. I was hoping to use the LIKE mysql clause in my query, such as (ex)

SELECT sender ， subject ，消息 FROM 消息 WHERE 消息 LIKE'％EXAMPLE_QUERY％';

SELECT sender, subject, message FROM Messages WHERE message LIKE '%EXAMPLE_QUERY%';

检索结果。遗憾的是，当存在前导通配符时，MySQL不使用索引，这对于搜索查询可能出现在消息中的任何位置（这是通配符的工作原理，不是吗？）。查询非常慢，我也不能使用全文索引，因为烦人的50％规则（我只能负担得起这么多）。无论如何（或者甚至是任何替代方案）使用like和两个通配符来优化查询？任何帮助都表示赞赏。

to retrieve results. unfortunately, MySQL doesn't use indexes when a leading wildcard is present , and this is necessary for the search query could appear anywhere in the message (this is how the wildcards work, no?). Queries are very very slow and I cannot use a full text index either, because of the annoying 50% rule (I just can't afford to rule that much out). Is there anyway (or even, any alternative to this) to optimize a query using like and two wildcards? Any help is appreciated.

推荐答案

你应该使用全文索引（你说不能），设计一个完整的 - 自己搜索文本或从MySQL卸载搜索并使用Sphinx / Lucene。对于Lucene，您可以使用Zend Framework中的Zend_Search_Lucene实现或使用Solr。

You should either use full-text indexes (you said you can't), design a full-text search by yourself or offload the search from MySQL and use Sphinx/Lucene. For Lucene you can use Zend_Search_Lucene implementation from Zend Framework or use Solr.

MySQL中的正常索引是B + Trees，如果启动它们则不能使用它们string是未知的（当你在开头有通配符时就是这种情况）

Normal indexes in MySQL are B+Trees, and they can't be used if the starting of the string is not known (and this is the case when you have wildcard in the beginning)

另一种选择是使用引用表自己实现搜索。在单词中拆分文本并创建包含word，record_id的表。然后在搜索中，您将用单词拆分查询并搜索参考表中的每个单词。通过这种方式，你不会将自己限制在整个文本的开头，而只限于给定单词的开头（无论如何你将匹配其余的单词）

Another option is to implement search on your own, using reference table. Split text in words and create table that contains word, record_id. Then in the search you split the query in words and search for each of the words in the reference table. In this way you are not limitting yourself to the beginning of the whole text, but only to the beginning of the given word (and you'll match the rest of the words anyway)

这篇关于使用MySQL中的LIKE子句优化表搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！