mysql 按计数排序性能
我发现以下内容有点令人困惑......如果我执行以下查询，当按索引值“关键字”排序时，需要 0.0008 秒，但当按“计数”排序时，需要 3 秒以上。
以下大约需要 0.0008 秒：
SELECT keyword, COUNT(DISTINCT pmid) as count
FROM keywords
WHERE (collection_id = 13262022107433)
GROUP BY keyword
order by keyword desc limit 1;
这需要 3 秒以上：
SELECT keyword, COUNT(DISTINCT pmid) as count
FROM keywords
WHERE (collection_id = 13262022107433)
GROUP BY keyword
order by count desc limit 1;
按计数排序时，有没有办法加快结果集的排序速度？真的需要那么长的时间吗？还有其他选择吗？引擎是InnoDB。
非常感谢您的投入！
I'm finding the following a little perplexing... if I perform the below queries, when sorting by the indexed value 'keyword' it takes 0.0008 seconds, but when sorting by 'count' it takes over 3 seconds.
The following takes approx 0.0008 seconds:
SELECT keyword, COUNT(DISTINCT pmid) as count
FROM keywords
WHERE (collection_id = 13262022107433)
GROUP BY keyword
order by keyword desc limit 1;
This takes over 3 seconds:
SELECT keyword, COUNT(DISTINCT pmid) as count
FROM keywords
WHERE (collection_id = 13262022107433)
GROUP BY keyword
order by count desc limit 1;
Is there a way of speeding up a sort on a result set when sorting by count? Should it really take that much longer? Are there any alternatives? The engine is InnoDB.
Many thanks for your input!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可能需要添加额外的索引来协助计数阶段。
如果您已经有一个仅包含 collection_id 和关键字的复合索引，查询优化器仍将包含对表中 pmid 字段的查找。
通过添加这个新索引，这将删除任何表扫描并仅执行索引扫描。
这将加快查询的 count(distinct pmid) 部分。
尝试一下！
You may want to add an additional index to assist the in the counting phase.
If you already have a compound index with collection_id and keyword only, the Query Optimizer will still include a lookup for the pmid field from the table.
By adding this new index, this will remove any table scans and perform index scans only.
This will speed the count(distinct pmid) portion of the query.
Give it a Try !!!
并非意外，也无法避免。当这个查询按
关键字排序时，MySQL可以查看最后出现的关键字，挑选出具有该关键字的行，并对它们进行计数。但是，当您按count排序时，它必须计算每个关键字的行数才能找出最高的一个。还有很多工作要做！Not unexpected, not avoidable. When this query is ordered by
keyword, MySQL can just look at what keyword comes last, pick out the rows with that keyword, and count them. When you order bycount, though, it has to count the rows for every keyword to figure out which one is highest. That's a lot more work!