From 71b07d88ad342c08eefe403fc5f45e570c5e1676 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Wed, 29 Nov 2017 15:53:21 +0100
Subject: [inc/Paginate] Use Count(*) instead of SQL_CALC_FOUND_ROWS, which is
 faster

Most of the time.
---
 inc/paginate.inc.php   | 23 ++++++++++++-----------
 inc/pagination.inc.php | 47 -----------------------------------------------
 2 files changed, 12 insertions(+), 58 deletions(-)
 delete mode 100644 inc/pagination.inc.php

(limited to 'inc')

diff --git a/inc/paginate.inc.php b/inc/paginate.inc.php
index 91f52077..cdb4adf1 100644
--- a/inc/paginate.inc.php
+++ b/inc/paginate.inc.php
@@ -38,11 +38,9 @@ class Paginate
 			if (preg_match('/(\-\-|;)(\s|[^\'"`])*$/is', $query)) {
 				Util::traceError("Your query must not end in a comment or semi-colon!");
 			}
-			$query .= ' LIMIT ' . ($this->currentPage * $this->perPage) . ', ' . $this->perPage;
-			// Use SQL_CALC_FOUND_ROWS
-			if (!preg_match('/^\s*SELECT\s+SQL_CALC_FOUND_ROWS/is', $query)) {
-				$query = preg_replace('/^\s*SELECT/is', 'SELECT SQL_CALC_FOUND_ROWS ', $query);
-			}
+			// Don't use SQL_CALC_FOUND_ROWS as it leads to filesort frequently thus being slower than two queries
+			// See https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
+
 		} else {
 			Util::traceError('Unsupported database engine');
 		}
@@ -55,7 +53,7 @@ class Paginate
 			if (substr($url, -1) !== '&') $url .= '&';
 		}
 		//
-		$this->query =$query;
+		$this->query = $query;
 		$this->url = $url;
 	}
 
@@ -64,11 +62,14 @@ class Paginate
 	 */
 	public function exec($args = array())
 	{
-		$args[':limit_start'] = $this->currentPage;
-		$args[':limit_count'] = $this->perPage;
-		$retval = Database::simpleQuery($this->query, $args);
-		$res = Database::queryFirst('SELECT FOUND_ROWS() AS rowcount');
-		$this->totalRows = (int)$res['rowcount'];
+		$countQuery = preg_replace('/ORDER\s+BY\s.*?(\sASC|\sDESC|$)/is', '', $this->query);
+		$countQuery = preg_replace('/SELECT\s.*?\sFROM\s/is', 'SELECT Count(*) AS rowcount FROM ', $countQuery);
+		$countRes = Database::queryFirst($countQuery, $args);
+		$args['limit_start'] = $this->currentPage;
+		$args['limit_count'] = $this->perPage;
+		$query = $this->query . ' LIMIT ' . ($this->currentPage * $this->perPage) . ', ' . $this->perPage;
+		$retval = Database::simpleQuery($query, $args);
+		$this->totalRows = (int)$countRes['rowcount'];
 		return $retval;
 	}
 
diff --git a/inc/pagination.inc.php b/inc/pagination.inc.php
deleted file mode 100644
index 65785a36..00000000
--- a/inc/pagination.inc.php
+++ /dev/null
@@ -1,47 +0,0 @@
-<?php
-
-/**
- * TODO: Why does this class exist?
- * There's already the Paginate class which works more efficient by using the LIMIT statement
- * for the query, and has more options. Consider refactoring the places where this class is
- * used (see syslog or eventlog for usage examples), then get rid of this one.
- */
-class Pagination
-{
-	private $items;
-	private $page;
-	private $maxItems;
-
-	public function __construct($par1, $par2)
-	{
-		$this->items = $par1;
-		$this->page = $par2;
-
-		$this->maxItems = 5;
-	}
-
-	public function getPagination()
-	{
-		$ret = array();
-		$n = ceil(count($this->items) / $this->maxItems);
-		for ($i = 1; $i <= $n; $i++) {
-			$class = ($i == $this->page) ? 'active' : '';
-			$ret[] = array(
-				'class' => $class,
-				'page' => $i
-			);
-		}
-		return $ret;
-	}
-
-	public function getItems()
-	{
-		$ret = array();
-		$first = ($this->page - 1) * $this->maxItems;
-		for ($i = 0; $i < $this->maxItems; $i++) {
-			if ($first + $i < count($this->items))
-				$ret[] = $this->items[$first + $i];
-		}
-		return $ret;
-	}
-}
\ No newline at end of file
-- 
cgit v1.2.3-55-g7522