Does ORDER BY COUNT(*) DESC suffer a penalty? I understand that older versions of MySQL parse the following but has no effect:

INDEX (col DESC)


For the following example, does performance suffer and is there a way to overcome it?

  SELECT col, COUNT(*)
    FROM tbl
GROUP BY col
ORDER BY COUNT(*) DESC
The optimizer might build a hash table -- key == values of col, value = count.  Or it might extract all the col values.  But in either case, it will have to do some kind of sort at some point.

When everything fits in RAM, the cost of the sort is minimal.  As a rule of thumb, when the GROUP BY and the ORDER BY are different, then there will be at least one sort, sometimes two.

The only optimization you can do for that query is INDEX(col).  Then, at least, it is working with the index BTree, and the values of col are known to be sorted.  Hence it walks through the index counting up the counts.  Still there is a sort at the end.

As for whether DESC costs anything versus ASC, yes and no.  In the B+Trees that InnoDB uses, it is slightly more efficient to walk forward (ASC) than backward (DESC).  But that is not relevant here.  The sort for the ORDER BY does not use a BTree if it is in RAM -- Instead it is a Quicksort (or something).

Or is this what you are asking?

  SELECT col, COUNT(*)
    FROM tbl
GROUP BY col
ORDER BY COUNT(*) DESC


versus

  SELECT col, COUNT(*)
    FROM tbl
GROUP BY col
-- no ORDER BY


There definitely is an extra sort.  Without the ORDER BY (ASC or DESC), you will probably get the output sorted by col.  In fact, until a recent version, you were guaranteed to get it sorted by the GROUP BY.  This was an extension to the standard, that has been removed.