On Sat 29 Jan 2011 15:50 -0600, Dan McGee wrote:
LocationID, DummyPkg- are these used anymore on the Packages table? If not, getting rid of these would do wonders toward actually allowing MySQL to use indexes for some queries, which it has been unable to do for a long time. I've pasted some of the slow query log analysis below from stuff this month, it is pretty obvious where work should be focused.
Killing these two fields and then replacing the SQL_CALC_FOUND_ROWS with a second count query should take care of nearly every slow query that currently happens.
Also potentially worth attacking is the use of GREATEST(SubmittedTS,ModifiedTS) - since the wonderful MySQL has no functional indexes using this in a query as an order by clause results in full table scans.
LocationID doesn't really have much use anymore. DummyPkg is still used to track dependencies, but perhaps another strategy could be used to do that. Thanks for looking at that, and the patches. :D