[aur-dev] [PATCH 2/2] Specify explicit joins for package search

[Dan McGee]

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 <dan at archlinux.org>
---
 web/lib/pkgfuncs.inc |   12 ++++++------
 1 files changed, 6 insertions(+), 6 deletions(-)

diff --git a/web/lib/pkgfuncs.inc b/web/lib/pkgfuncs.inc
index 6ce9615..1c467ba 100644
--- a/web/lib/pkgfuncs.inc
+++ b/web/lib/pkgfuncs.inc
@@ -425,19 +425,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
-- 
1.6.2.2