API: SQL optimisation

https://github.com/marienfressinaud/FreshRSS/issues/13

4 files changed, 42 insertions, 22 deletions

diff --git a/app/Controllers/entryController.php b/app/Controllers/entryController.php
index 1756c91e5..ca7122a7c 100755
--- a/app/Controllers/entryController.php
+++ b/app/Controllers/entryController.php
@@ -137,11 +137,13 @@ class FreshRSS_entry_Controller extends Minz_ActionController {
 				if ($nb > 0) {
 					$nbTotal += $nb;
 					Minz_Log::record($nb . ' old entries cleaned in feed [' . $feed->url() . ']', Minz_Log::DEBUG);
-					$feedDAO->updateLastUpdate($feed->id());
+					//$feedDAO->updateLastUpdate($feed->id());
 				}
 			}
 		}
 
+		$feedDAO->updateCachedValues();
+
 		invalidateHttpCache();
 
 		$notif = array(
diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index 3fc7a05ef..e90b9a7fe 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -35,11 +35,15 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo {
 		}
 	}
 
-	public function markFavorite ($id, $is_favorite = true) {
+	public function markFavorite($ids, $is_favorite = true) {
+		if (!is_array($ids)) {
+			$ids = array($ids);
+		}
 		$sql = 'UPDATE `' . $this->prefix . 'entry` e '
 		     . 'SET e.is_favorite = ? '
-		     . 'WHERE e.id=?';
-		$values = array ($is_favorite ? 1 : 0, $id);
+		     . 'WHERE e.id IN (' . str_repeat('?,', count($ids) - 1). '?)';
+		$values = array ($is_favorite ? 1 : 0);
+		$values = array_merge($values, $ids);
 		$stm = $this->bd->prepare ($sql);
 		if ($stm && $stm->execute ($values)) {
 			return $stm->rowCount();
@@ -49,6 +53,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo {
 			return false;
 		}
 	}
+
 	public function markRead ($id, $is_read = true) {
 		$sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id '
 		     . 'SET e.is_read = ?,'
@@ -64,6 +69,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo {
 			return false;
 		}
 	}
+
 	public function markReadEntries ($idMax = 0, $onlyFavorites = false, $priorityMin = 0) {
 		if ($idMax == 0) {
 			$sql = 'UPDATE `' . $this->prefix . 'entry` e INNER JOIN `' . $this->prefix . 'feed` f ON e.id_feed = f.id '
@@ -323,7 +329,7 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo {
 		return isset ($entries[0]) ? $entries[0] : null;
 	}
 
-	public function listWhere($type = 'a', $id = '', $state = 'all', $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0, $keepHistoryDefault = 0) {
+	private function sqlListWhere($type = 'a', $id = '', $state = 'all', $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0, $keepHistoryDefault = 0) {
 		$where = '';
 		$joinFeed = false;
 		$values = array();
@@ -432,14 +438,22 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo {
 			}
 		}
 
+		return array($values,
+			'SELECT e1.id FROM `' . $this->prefix . 'entry` e1 '
+			. ($joinFeed ? 'INNER JOIN `' . $this->prefix . 'feed` f ON e1.id_feed = f.id ' : '')
+			. 'WHERE ' . $where
+			. $search
+			. 'ORDER BY e1.id ' . $order
+			. ($limit > 0 ? ' LIMIT ' . $limit : ''));	//TODO: See http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
+	}
+
+	public function listWhere($type = 'a', $id = '', $state = 'all', $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0, $keepHistoryDefault = 0) {
+		list($values, $sql) = $this->sqlListWhere($type, $id, $state, $order, $limit, $firstId, $filter, $date_min, $keepHistoryDefault);
+
 		$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 (SELECT e1.id FROM `' . $this->prefix . 'entry` e1 '
-			     . ($joinFeed ? 'INNER JOIN `' . $this->prefix . 'feed` f ON e1.id_feed = f.id ' : '')
-			     . 'WHERE ' . $where
-			     . $search
-			     . 'ORDER BY e1.id ' . $order
-			     . ($limit > 0 ? ' LIMIT ' . $limit : '')	//TODO: See http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
+		     . 'INNER JOIN ('
+		     . $sql
 		     . ') e2 ON e2.id = e.id '
 		     . 'ORDER BY e.id ' . $order;
 
@@ -449,6 +463,15 @@ class FreshRSS_EntryDAO extends Minz_ModelPdo {
 		return self::daoToEntry ($stm->fetchAll (PDO::FETCH_ASSOC));
 	}
 
+	public function listIdsWhere($type = 'a', $id = '', $state = 'all', $order = 'DESC', $limit = 1, $firstId = '', $filter = '', $date_min = 0, $keepHistoryDefault = 0) {
+		list($values, $sql) = $this->sqlListWhere($type, $id, $state, $order, $limit, $firstId, $filter, $date_min, $keepHistoryDefault);
+
+		$stm = $this->bd->prepare($sql);
+		$stm->execute($values);
+
+		return $stm->fetchAll(PDO::FETCH_COLUMN, 0);
+	}
+
 	public function listLastGuidsByFeed($id, $n) {
 		$sql = 'SELECT guid FROM `' . $this->prefix . 'entry` WHERE id_feed=? ORDER BY id DESC LIMIT ' . intval($n);
 		$stm = $this->bd->prepare ($sql);
diff --git a/app/Models/FeedDAO.php b/app/Models/FeedDAO.php
index fb4a847a0..f9e1ad9e8 100644
--- a/app/Models/FeedDAO.php
+++ b/app/Models/FeedDAO.php
@@ -242,6 +242,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo {
 
 		return $res[0]['count'];
 	}
+
 	public function countNotRead ($id) {
 		$sql = 'SELECT COUNT(*) AS count FROM `' . $this->prefix . 'entry` WHERE id_feed=? AND is_read=0';
 		$stm = $this->bd->prepare ($sql);
@@ -251,6 +252,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo {
 
 		return $res[0]['count'];
 	}
+
 	public function updateCachedValues () {	//For one single feed, call updateLastUpdate($id)
 		$sql = 'UPDATE `' . $this->prefix . 'feed` f '
 		     . 'INNER JOIN ('
@@ -263,9 +265,7 @@ class FreshRSS_FeedDAO extends Minz_ModelPdo {
 		     . 'SET f.cache_nbEntries=x.nbEntries, f.cache_nbUnreads=x.nbUnreads';
 		$stm = $this->bd->prepare ($sql);
 
-		$values = array ($feed_id);
-
-		if ($stm && $stm->execute ($values)) {
+		if ($stm && $stm->execute()) {
 			return $stm->rowCount();
 		} else {
 			$info = $stm->errorInfo();
diff --git a/p/api/greader.php b/p/api/greader.php
index 035a031dd..d1846fdaf 100644
--- a/p/api/greader.php
+++ b/p/api/greader.php
@@ -403,17 +403,12 @@ function streamContentsItemsIds($streamId, $start_time, $count, $order, $exclude
 	}
 
 	$entryDAO = new FreshRSS_EntryDAO();
-	$entries = $entryDAO->listWhere($type, $id, $state, $order === 'o' ? 'ASC' : 'DESC', $count, '', '', $start_time);
+	$ids = $entryDAO->listIdsWhere($type, $id, $state, $order === 'o' ? 'ASC' : 'DESC', $count, '', '', $start_time);
 
 	$itemRefs = array();
-	foreach ($entries as $entry) {
-		$f_id = $entry->feed();
+	foreach ($ids as $id) {
 		$itemRefs[] = array(
-			'id' => $entry->id(),	//64-bit decimal
-			//'timestampUsec' => $entry->dateAdded(true),
-			/*'directStreamIds' => array(
-				'feed/' . $entry->feed()
-			),*/
+			'id' => $id,	//64-bit decimal
 		);
 	}