CODEWITHSUNDEEP
mysqlperformancecountsql-order-by
DrNoFruit
DrNoFruit

Reputation: 185

mysql order by count performance

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!

Upvotes: 2

Views: 3347

Answers (2)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

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);

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 !!!

Upvotes: 4

user149341
user149341

Reputation:

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 by count, though, it has to count the rows for every keyword to figure out which one is highest. That's a lot more work!

Upvotes: 4

Related Questions