mysql按计数排序性能 [英] mysql order by count performance

问题描述

我发现以下内容有点令人困惑...如果我执行以下查询，按索引值关键字"排序时需要 0.0008 秒，但按计数"排序时需要 3 秒以上.

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.

以下内容大约需要 0.0008 秒:

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;

这需要超过 3 秒:

SELECT keyword, COUNT(DISTINCT pmid) as count 
    FROM keywords 
    WHERE (collection_id = 13262022107433) 
    GROUP BY keyword 
    order by count desc limit 1;

在按计数排序时，有没有办法加快对结果集的排序?真的需要那么久吗?有没有其他选择?引擎是 InnoDB.

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.

非常感谢您的投入！

推荐答案

您可能需要添加一个额外的索引来帮助计数阶段.

You may want to add an additional index to assist the in the counting phase.

ALTER TABLE keywords ADD INDEX ckp_index (collection_id,keyword,pmid);

如果您已经有一个仅包含 collection_id 和关键字的复合索引，查询优化器仍将包含对表中 pmid 字段的查找.

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.

这将加快查询的计数(不同 pmid)部分.

This will speed the count(distinct pmid) portion of the query.

试一试！！！

这篇关于mysql按计数排序性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！