MySQL 5.6 InnoDB全文搜索 [英] MySQL 5.6 InnoDB Full Text Search

问题描述

我意识到MySQL 5.6仍处于测试阶段，但是有没有人有过使用新InnoDB FTS引擎的经验？它与Sphinx之类的东西相比如何？

I realize that MySQL 5.6 is still in beta, but does anyone have experience using the new InnoDB FTS engine? How does it compare to something like Sphinx?

感谢
Jason

Thanks Jason

推荐答案

从来没有使用Sphinx，但在Innodb表上使用MySQL 5.6 FTS，大约有170k行。在名称列上创建一个FTS索引（包含一个人的所有姓名）。在字符串 MATCH（name）AGAINST（+ word *）中找到一个字在BOOLEAN MODE 中的工作速度要快很多（在我的2-3次）使用名称LIKEword％或名称LIKE％word。但是，在进行连接时，请检查EXPLAIN以查看是否实际使用了FTS索引。看起来MySQL优化器并不擅长猜测何时应该使用FTS索引。

Never used Sphinx, but tried MySQL 5.6 FTS on an Innodb table with about 170k rows. Made an FTS index on the name column (contains all names of a person). To find a word in any position of the string MATCH(name) AGAINST("+word*") IN BOOLEAN MODE does work a lot faster (2-3 times in my case) than using name LIKE "word%" OR name LIKE "% word". However when making joins do check EXPLAIN to see if the FTS index is actually used. It seems MySQL optimizer is not that good at guessing when the FTS index should be used.

这篇关于MySQL 5.6 InnoDB全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！