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

[Dan McGee]

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