summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorDan McGee <dan@archlinux.org>2011-03-02 08:35:39 -0600
committerLukas Fleischer <archlinux@cryptocrack.de>2011-03-10 18:16:00 +0100
commitc34bebf42859019998c7ff3c03b570295ae83ebe (patch)
treebe0e6b04cd45a967b8ec71dc86aba8cc5c7455e7
parent2cfcddf24e4f1f20b70b300111bd92d77c0e2471 (diff)
downloadaurweb-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.inc18
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"){