对包含3,500,000行的表进行查询优化，建立索引

Question

我有一个产品表，其中大约有3500,000个项目。该表在名为title的列上具有全文索引。

下面的查询示例耗时长达4秒，这是完全不可接受的，我正在寻找优化的方法...感谢您的点子/建议。

基本上；哪些列应该被索引，我应该单独拉出这么多列，还是更快地拉出所有列(总共23列)等等

查询示例：

SELECT PRid, title, Artist, author, actors, imageURL, prodcat, lowprice,
highprice, prodcatID, description from products where
((MATCH(title) AGAINST ('+blue +top' IN BOOLEAN MODE))) order by
userrating desc limit 500 

Answer 1

要查看MySQL正在做什么，您的第一个调用端口应该始终是EXPLAIN。我怀疑对比赛分数以外的其他内容进行排序可能没有帮助--我不认为mysql能够在对全文索引的同时使用索引userrating进行排序。

上次我使用MySQL全文搜索(诚然是几年前)时，我发现它的速度不够快，超过一百万行左右。您可能希望考虑使用全文搜索引擎，如cloudsearch、elasticsearch、solr或sphinx。

Answer 2

"title“有多少个独特的组合？您可能会发现，将该数据放入具有主数据表中的外键的查找表中是值得的。

然后，在查询数据时，将两个表连接在一起，并将筛选器应用于较小的表，这将花费较少的时间。然后，联接发生在一个数值字段上，您可以在主数据表中对其进行索引。

这比在3.5m行上进行文本过滤要快得多。假设该数据中有许多重复项，那么您的查找表可能只有这个大小的一小部分。

您还会发现，如果使用数值字段而不是文本字段，则读取主数据会更快，因为行会更小，因此整个数据大小也会更小。

将所有基于文本的数据放入查找中将使一切变得更快，因为主数据表可以变成固定格式而不是动态格式，这意味着在表中定位数据时索引更有效。

希望这能有所帮助！

戴夫