From 1e077160fca3306a273ecae5a366fd756c32baee Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Sat, 30 Nov 2013 22:47:48 +0100
Subject: Optimisation recherche et pagination
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

* Optimisation recherche SQL avec utilisation de HAVING plutôt que WHERE
* Simplification et amélioration des performances en supprimant de
RSSPaginator qui n'aidait plus vraiment et nécessitait plus de code et
des copies de données.
* Correction d'un bug dans le titre de la page introduit récemment, et
simplification
---
 app/models/Entry.php        | 11 ++++++++---
 app/models/RSSPaginator.php | 37 -------------------------------------
 2 files changed, 8 insertions(+), 40 deletions(-)
 delete mode 100644 app/models/RSSPaginator.php

(limited to 'app/models')

diff --git a/app/models/Entry.php b/app/models/Entry.php
index 915fbccc8..ae8facf68 100755
--- a/app/models/Entry.php
+++ b/app/models/Entry.php
@@ -504,14 +504,18 @@ class EntryDAO extends Model_pdo {
 		if ($firstId > 0) {
 			$where .= 'AND e.id ' . ($order === 'DESC' ? '<=' : '>=') . $firstId . ' ';
 		}
-		$terms = explode(' ', trim($filter));
+		$terms = array_unique(explode(' ', trim($filter)));
 		sort($terms);	//Put #tags first
+		$having = '';
 		foreach ($terms as $word) {
 			if (!empty($word)) {
 				if ($word[0] === '#' && isset($word[1])) {
-					$where .= 'AND tags LIKE "%' . $word . '%" ';
+					$having .= 'AND tags LIKE ? ';
+					$values[] = '%' . $word .'%';
 				} elseif (!empty($word)) {
-					$where .= 'AND (e.title LIKE "%' . $word . '%" OR UNCOMPRESS(e.content_bin) LIKE "%' . $word . '%") ';
+					$having .= 'AND (e.title LIKE ? OR content LIKE ?) ';
+					$values[] = '%' . $word .'%';
+					$values[] = '%' . $word .'%';
 				}
 			}
 		}
@@ -519,6 +523,7 @@ class EntryDAO extends Model_pdo {
 		$sql = 'SELECT e.id, e.guid, e.title, e.author, UNCOMPRESS(e.content_bin) AS content, e.link, e.date, e.is_read, e.is_favorite, e.id_feed, e.tags '
 		     . 'FROM `' . $this->prefix . 'entry` e '
 		     . 'INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id WHERE ' . $where
+		     . (empty($having) ? '' : 'HAVING' . substr($having, 3))
 		     . 'ORDER BY e.id ' . $order;
 
 		if ($limit > 0) {
diff --git a/app/models/RSSPaginator.php b/app/models/RSSPaginator.php
deleted file mode 100644
index 39146f1ba..000000000
--- a/app/models/RSSPaginator.php
+++ /dev/null
@@ -1,37 +0,0 @@
-<?php
-
-// Un système de pagination beaucoup plus simple que Paginator
-// mais mieux adapté à nos besoins
-class RSSPaginator {
-	private $items = array ();
-	private $next = '';
-
-	public function __construct ($items, $next) {
-		$this->items = $items;
-		$this->next = $next;
-	}
-
-	public function isEmpty () {
-		return empty ($this->items);
-	}
-
-	public function items () {
-		return $this->items;
-	}
-
-	public function next () {
-		return $this->next;
-	}
-
-	public function peek () {
-		return isset($this->items[0]) ? $this->items[0] : null;
-	}
-
-	public function render ($view, $getteur) {
-		$view = APP_PATH . '/views/helpers/'.$view;
-
-		if (file_exists ($view)) {
-			include ($view);
-		}
-	}
-}
-- 
cgit v1.2.3