[aur-dev] AUR slow queries, stale database columns, etc.

[Loui Chang]

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