From 1ceeda8ab529ba0ba04664621e9edb5f4dfae397 Mon Sep 17 00:00:00 2001 From: Dan McGee Date: Sat, 11 Apr 2009 16:40:58 -0500 Subject: Specify explicit joins for package search Refactor the query to use explicit LEFT JOINs, which appears to be handled by MySQL in a saner fashion than the previous implicit join syntax. This is part two in a slight fixup for observed slow queries in the production environment. With the new indexes and this fixup, a particular iteration of this query will examine only 13346 rows instead of 272060. Signed-off-by: Dan McGee Signed-off-by: Loui Chang --- web/lib/pkgfuncs.inc | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'web') diff --git a/web/lib/pkgfuncs.inc b/web/lib/pkgfuncs.inc index 056552b..a4a2dfc 100644 --- a/web/lib/pkgfuncs.inc +++ b/web/lib/pkgfuncs.inc @@ -437,19 +437,19 @@ function pkg_search_page($SID="") { Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate - FROM PackageCategories, PackageLocations, Packages + FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) "; - if ($SID) { $q .= "LEFT JOIN PackageVotes ON (Packages.ID = PackageVotes.PackageID AND PackageVotes.UsersID = $myuid) LEFT JOIN CommentNotify ON (Packages.ID = CommentNotify.PkgID AND CommentNotify.UserID = $myuid) "; } - $q .= "WHERE - Packages.CategoryID = PackageCategories.ID - AND Packages.LocationID = PackageLocations.ID - AND Packages.DummyPkg = 0 "; + $q .= "LEFT JOIN PackageCategories + ON (Packages.CategoryID = PackageCategories.ID) + LEFT JOIN PackageLocations + ON (Packages.LocationID = PackageLocations.ID) + WHERE Packages.DummyPkg = 0 "; // TODO: possibly do string matching on category and // location to make request variable values more sensible -- cgit v1.2.3-70-g09d2