Re: Optimising a GROUP BY and ORDER BY in a joined query

[Rick James]

You and nine zillion others are wrong. (Sorry, I feel like ranting.) "temporary" and "filesort" are not the real villains; it is (in your case) the need to do a full table scan of 10819 rows in the first table, then look up 10819 rows one by one in the second table. (At least, that is what the optimizer estimates.)

The maligned "filesort" may be as simple as a temporary MEMORY table with everything done in RAM; no actual I/O. (I can't tell for sure.)

The GROUP BY and ORDER BY require that it find all 10819 possible rows, sort them, then deliver the first 100 (LIMIT 100). Without the GROUP BY and ORDER BY, any 100 will do, so it can finish much faster. (On the order of 10819/100 times as fast.)

What's the value if key_buffer_size? How much RAM do you have? And do SHOW TABLE STATUS. (I want to see how big they are.)

(Don't worry; I'll get to your real question in a minute.)

Here's the killer:
WHERE VC_Valid = 1
GROUP BY VP_Family
One of those fields is in one table; the other field is in the other table.

Probably most rows have VC_Valid = 1, correct? That means that an index on that field would be not as efficient as a table scan, tossing the non-1 rows as it goes.

Give this a try. I don't know if it will work.

SELECT  p.VP_Family
   FROM          vod_package p
   STRAIGHT_JOIN vod_control c
         ON c.VC_AssetId = p.VP_AssetId 
   WHERE c.VC_Valid = 1 
   GROUP BY p.VP_Family
   ORDER BY p.VP_Family
   LIMIT 100;

This "should" eliminate the filesort. But, more importantly, it "should" finish long before running through the entire tables.

Please let me know if that technique works.