搜索比类似‘%omething.%’更快的查询

Question

我有一个很大的域名列表，还有一个很大的单词列表。我想看看在我的列表中有多少域名的末尾有这些单词。我尝试了2个查询，但都花费了太多的时间来执行。尝试找出是否有任何方法可以使查询速度更快。

首先我尝试了这个查询，大约需要50分钟才能返回结果：

SELECT COUNT(*) AS count 
FROM   table 
WHERE  domain_name LIKE '%my_word.%'; 

然后我想如果我去掉它的.%，也许它会更快，所以我试了一下，但它仍然很慢：

SELECT COUNT(*) AS count 
FROM   table 
WHERE  SUBSTRING_INDEX(domain_name, '.', 1) LIKE '%my_word'; 

有没有人知道更快的查询方法？

谢谢。

Answer 1

为了获得最佳结果，您需要在数据上创建全文索引，请使用innoDb mysql引擎

例如

SELECT  *
FROM    domain_name
WHERE   MATCH(data) AGAINST ('+word1 +word2 +word3' IN BOOLEAN MODE)

参考http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

Answer 2

你的问题可以通过全文索引来解决，但是它只在innodb的5.6版本中兼容，并且你可以在myisam表的任何版本中使用。

通过以下命令创建全文索引-

ALTER TABLE my_table ADD FULLTEXT(domain_name);

现在你可以按照下面的方法使用查询-

SELECT count(*) FROM my_table WHERE MATCH (domain_name) AGAINST ('your_word');

Answer 3

由于您的查询不是寻找单独的单词，我认为它不会从全文索引中受益(引擎不知道如何将域解析为单独的单词)。我也不相信substring解决方案会产生比LIKE运算符更好的性能。您正在处理简单的字符串匹配，数据库不是运行它的最佳位置。

也就是说，我认为提高这个查询速度的最好方法是减少需要从磁盘读取的字节数。您可以通过在该列上创建一个简单的索引来实现这一点(查询仍然是索引扫描，但它比表扫描更便宜)。您可以做的另一件事是从该列中删除不必要的字符，如.com或.net，因为这将允许在每次访问磁盘时获取更有意义的数据。