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. -Dan $ mysqldumpslow -s c sigurd-slow.log | head -n 100 Reading mysql slow query log from sigurd-slow.log Count: 3550 Time=2.08s (7390s) Lock=0.00s (3s) Rows=26.7 (94644), aur[aur]@localhost SELECT SQL_CALC_FOUND_ROWS Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY NumVotes DESC, Name ASC, CategoryID DESC LIMIT N, N Count: 2275 Time=1.38s (3143s) Lock=0.00s (0s) Rows=25.4 (57680), aur[aur]@localhost SELECT SQL_CALC_FOUND_ROWS CommentNotify.UserID AS Notify, PackageVotes.UsersID AS Voted, Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageVotes ON (Packages.ID = PackageVotes.PackageID AND PackageVotes.UsersID = N) LEFT JOIN CommentNotify ON (Packages.ID = CommentNotify.PkgID AND CommentNotify.UserID = N) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY NumVotes DESC, Name ASC, CategoryID DESC LIMIT N, N Count: 350 Time=1.62s (566s) Lock=0.01s (1s) Rows=27.4 (9591), aur[aur]@localhost SELECT SQL_CALC_FOUND_ROWS Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY NumVotes ASC, Name ASC, CategoryID DESC LIMIT N, N Count: 307 Time=1.20s (368s) Lock=0.00s (0s) Rows=65.8 (20200), aur[aur]@localhost SELECT SQL_CALC_FOUND_ROWS CommentNotify.UserID AS Notify, PackageVotes.UsersID AS Voted, Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageVotes ON (Packages.ID = PackageVotes.PackageID AND PackageVotes.UsersID = N) LEFT JOIN CommentNotify ON (Packages.ID = CommentNotify.PkgID AND CommentNotify.UserID = N) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY GREATEST(SubmittedTS,ModifiedTS) DESC, Name ASC, LocationID ASC LIMIT N, N Count: 220 Time=1.76s (386s) Lock=0.00s (0s) Rows=29.9 (6585), aur[aur]@localhost SELECT SQL_CALC_FOUND_ROWS Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY Name DESC, LocationID ASC, CategoryID DESC LIMIT N, N Count: 218 Time=1.31s (286s) Lock=0.00s (0s) Rows=28.1 (6122), aur[aur]@localhost SELECT SQL_CALC_FOUND_ROWS Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY Name ASC, LocationID ASC, CategoryID DESC LIMIT N, N Count: 187 Time=2.21s (413s) Lock=0.00s (0s) Rows=26.9 (5034), aur[aur]@localhost SELECT SQL_CALC_FOUND_ROWS Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY LocationID ASC, Name ASC, CategoryID DESC LIMIT N, N