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

[Rick James]

Alas, I can't fix everything.

Hmmm... I should have caught this -- The query (yours or mine) as written does not need the GROUP BY; see what happens without it.

Your key_buffer_size is plenty big to handle the indexes for both tables, so there is no thrashing there.

Do you have to get exactly 100? What if you got only 99? Or asked for 110 just in case?

SELECT p.VP_Family
   FROM (
      SELECT VP_Family
         FROM vod_package
         ORDER BY VP_Family
         LIMIT 110    -- note: a few extra
        ) p
   JOIN vod_control c  ON c.VC_AssetId = p.VP_AssetId 
   WHERE c.VC_Valid = 1 
   ORDER BY p.VP_Family
   LIMIT 100;     -- hope that there are still 100 after checking VC_Valid

(Yes, both ORDER BY and LIMIT clauses are needed.)
The inner query should not have to hit more than 110 items, not 10819.

Is that the entire query? Or are you fetching more fields? (It may complicate this latest version.)

Will you be doing a second query with LIMIT 100,100? Don't. Instead "pick up where you left off". (If needed, I can point you at my rants about "pagination via LIMIT".)