diff options
| author | Dan McGee <dan@archlinux.org> | 2011-03-02 08:35:39 -0600 |
|---|---|---|
| committer | Lukas Fleischer <archlinux@cryptocrack.de> | 2011-03-10 18:16:00 +0100 |
| commit | c34bebf42859019998c7ff3c03b570295ae83ebe (patch) | |
| tree | be0e6b04cd45a967b8ec71dc86aba8cc5c7455e7 | |
| parent | 2cfcddf24e4f1f20b70b300111bd92d77c0e2471 (diff) | |
| download | aurweb-c34bebf42859019998c7ff3c03b570295ae83ebe.tar.xz | |
Perform a second query to find total search count
This removes the need for SQL_CALC_FOUND_ROWS which can really slow down
queries in a lot of cases. The COUNT(*) query we end up performing can
reuse a lot of the original clauses from our primary query, but we can
really slim it up by omitting some joins and the sorting/limiting
clauses.
Signed-off-by: Dan McGee <dan@archlinux.org>
Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
| -rw-r--r-- | web/lib/pkgfuncs.inc | 18 |
1 files changed, 11 insertions, 7 deletions
diff --git a/web/lib/pkgfuncs.inc b/web/lib/pkgfuncs.inc index e1505e7..d5e0771 100644 --- a/web/lib/pkgfuncs.inc +++ b/web/lib/pkgfuncs.inc @@ -434,7 +434,7 @@ function pkg_search_page($SID="") { // build the package search query // - $q_select = "SELECT SQL_CALC_FOUND_ROWS "; + $q_select = "SELECT "; if ($SID) { $q_select .= "CommentNotify.UserID AS Notify, PackageVotes.UsersID AS Voted, "; @@ -445,15 +445,18 @@ function pkg_search_page($SID="") { Packages.ID, Packages.OutOfDateTS "; $q_from = "FROM Packages - LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) "; + LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) + LEFT JOIN PackageCategories + ON (Packages.CategoryID = PackageCategories.ID) "; if ($SID) { - $q_from .= "LEFT JOIN PackageVotes + # this portion is not needed for the total row count query + $q_from_extra = "LEFT JOIN PackageVotes ON (Packages.ID = PackageVotes.PackageID AND PackageVotes.UsersID = $myuid) LEFT JOIN CommentNotify ON (Packages.ID = CommentNotify.PkgID AND CommentNotify.UserID = $myuid) "; + } else { + $q_from_extra = ""; } - $q_from .= "LEFT JOIN PackageCategories - ON (Packages.CategoryID = PackageCategories.ID) "; $q_where = "WHERE Packages.DummyPkg = 0 "; // TODO: possibly do string matching on category @@ -530,10 +533,11 @@ function pkg_search_page($SID="") { $q_limit = "LIMIT ".$_GET["O"].", ".$_GET["PP"]; - $q = $q_select . $q_from . $q_where . $q_sort . $q_limit; + $q = $q_select . $q_from . $q_from_extra . $q_where . $q_sort . $q_limit; + $q_total = "SELECT COUNT(*) " . $q_from . $q_where; $result = db_query($q, $dbh); - $total = mysql_result(db_query('SELECT FOUND_ROWS() AS Total', $dbh), 0); + $total = mysql_result(db_query($q_total, $dbh), 0); if ($result && $total > 0) { if (isset($_GET["SO"]) && $_GET["SO"] == "d"){ |