Re: GROUP BY ,ORDER BY optimization help

[Rick James]

> still don't get it why is not using `rating` from the `top_rated` key,
EXPLAIN fails to show any ORDER BY fields that it might be using.
It estimates that there are 2116 rows WHERE g.`active`='Yes' AND g.unhacked=0.
I believe that it is actually using `rating` and stopping after 20 rows.

> I can't understand is why is showing the result in ASC order
That is because the outer query has no ORDER BY. (The inner ORDER BY does not propagate out.)

Suggest you use the mysql commandline utility and execute the subquery by itself. You should see that it is getting the 20 rows quickly.

> is that bad filesort...
Filesort is sometimes necessary. Here's a guess: MySQL might not be smart enough to avoid filesort when more than 2 fields (active, unhacked, rating) are involved.

Still, sorting 20 rows is trivial. It would probably not touch the disk. (The term "filesort" includes cases where it can be done in memory.) By monitoring certain Com_% settings, you could actually find out if it "created a temp table on disk".

So, I think your final query will work something like this:
1. Look in games, using the index, and hitting either 20 or 2116 rows. It will grab only 20 rows.
2. filesort those 20 rows.
3. Reach into the other two tables using efficient keys, 20 times each.
4. Construct the final result according to the GROUP BY, and GROUP_CONCAT.
5. If you add "ORDER BY gd.rating DESC", then do another filesort (of only 20 rows) to get the final output.

Use the mysql commandline to experiment with the new query.