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

[Peter Brawley]

Michael,

Does performance improve when you add VC_Valid to the vod_control PK, move VP_Family from the vod_package VP_FamilyIndex key to the vod_package PK, and write the query this way ...

SELECT p.VP_Family 
FROM vod_package p
JOIN vod_control c ON p.VP_AssetId = c.VC_AssetId AND c.VC_Valid = 1
ORDER BY p.VP_Family;

Then, what happens to performance if you give vod_control a surrogate (auto_inrement) PK, replace the vod_package PK with a reference to that, and adjust the query to use the new keys?

PB
http://www.artfulsoftware.com