From f855dbdca6e95ac367b7a9dae9d3a866e1f85d37 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Mon, 2 Sep 2013 22:06:51 +0200 Subject: SQL and model optimisation Big effect (on speed and memory), but few changes :-) Drastically reduced the number of SQL requests needed (from 233 down to 8 to load the home page with my own data set = 140 feeds in 15 categories). Drastically reduced the amount of data transferred from MySQL to PHP. --- app/models/Entry.php | 97 ++++++++++++++++++++++++++++++++-------------------- 1 file changed, 59 insertions(+), 38 deletions(-) (limited to 'app/models/Entry.php') diff --git a/app/models/Entry.php b/app/models/Entry.php index c247c6362..4043e8f01 100755 --- a/app/models/Entry.php +++ b/app/models/Entry.php @@ -375,12 +375,15 @@ class EntryDAO extends Model_pdo { } } - public function listWhere ($where, $state, $order, $values = array ()) { + private function listWhere ($where, $state, $order, $limitFromId = '', $limitCount = '', $values = array ()) { if ($state == 'not_read') { $where .= ' AND is_read = 0'; } elseif ($state == 'read') { $where .= ' AND is_read = 1'; } + if (!empty($limitFromId)) { + $where .= ' AND date ' . ($order === 'low_to_high' ? '<=' : '>=') . ' (SELECT date from freshrss_entry WHERE id = "' . $limitFromId . '")'; + } if ($order == 'low_to_high') { $order = ' DESC'; @@ -390,78 +393,96 @@ class EntryDAO extends Model_pdo { $sql = 'SELECT e.* FROM ' . $this->prefix . 'entry e' . ' INNER JOIN ' . $this->prefix . 'feed f ON e.id_feed = f.id' . $where - . ' ORDER BY date' . $order; + . ' ORDER BY date' . $order . ', id' . $order; + + if (!empty($limitCount)) { + $sql .= ' LIMIT ' . $limitCount; //TODO: See http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ + } + $stm = $this->bd->prepare ($sql); $stm->execute ($values); return HelperEntry::daoToEntry ($stm->fetchAll (PDO::FETCH_ASSOC)); } - public function listEntries ($state, $order = 'high_to_low') { - return $this->listWhere (' WHERE priority > 0', $state, $order); + public function listEntries ($state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') { + return $this->listWhere (' WHERE priority > 0', $state, $order, $limitFromId, $limitCount + 1); } - public function listFavorites ($state, $order = 'high_to_low') { - return $this->listWhere (' WHERE is_favorite = 1', $state, $order); + public function listFavorites ($state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') { + return $this->listWhere (' WHERE is_favorite = 1', $state, $order, $limitFromId, $limitCount + 1); } - public function listPublic ($state, $order = 'high_to_low') { - return $this->listWhere (' WHERE is_public = 1', $state, $order); + public function listPublic ($state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') { + return $this->listWhere (' WHERE is_public = 1', $state, $order, $limitFromId, $limitCount + 1); } - public function listByCategory ($cat, $state, $order = 'high_to_low') { - return $this->listWhere (' WHERE category = ?', $state, $order, array ($cat)); + public function listByCategory ($cat, $state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') { + return $this->listWhere (' WHERE category = ?', $state, $order, $limitFromId, $limitCount + 1, array ($cat)); } - public function listByFeed ($feed, $state, $order = 'high_to_low') { - return $this->listWhere (' WHERE id_feed = ?', $state, $order, array ($feed)); + public function listByFeed ($feed, $state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') { + return $this->listWhere (' WHERE id_feed = ?', $state, $order, $limitFromId, $limitCount + 1, array ($feed)); } - public function count () { - $sql = 'SELECT COUNT(*) AS count FROM ' . $this->prefix . 'entry e INNER JOIN ' . $this->prefix . 'feed f ON e.id_feed = f.id WHERE priority > 0'; + public function countUnreadRead () { + $sql = 'SELECT is_read, COUNT(*) AS count FROM ' . $this->prefix . 'entry e INNER JOIN ' . $this->prefix . 'feed f ON e.id_feed = f.id WHERE priority > 0 GROUP BY is_read'; $stm = $this->bd->prepare ($sql); $stm->execute (); $res = $stm->fetchAll (PDO::FETCH_ASSOC); - return $res[0]['count']; + $readUnread = array('unread' => 0, 'read' => 0); + foreach ($res as $line) { + switch (intval($line['is_read'])) { + case 0: $readUnread['unread'] = intval($line['count']); break; + case 1: $readUnread['read'] = intval($line['count']); break; + } + } + return $readUnread; } - public function countNotRead () { - $sql = 'SELECT COUNT(*) AS count FROM ' . $this->prefix . 'entry e INNER JOIN ' . $this->prefix . 'feed f ON e.id_feed = f.id WHERE is_read=0 AND priority > 0'; - $stm = $this->bd->prepare ($sql); - $stm->execute (); - $res = $stm->fetchAll (PDO::FETCH_ASSOC); - - return $res[0]['count']; + public function count () { //Deprecated: use countUnreadRead() instead + $unreadRead = $this->countUnreadRead (); //This makes better use of caching + return $unreadRead['unread'] + $unreadRead['read']; + } + public function countNotRead () { //Deprecated: use countUnreadRead() instead + $unreadRead = $this->countUnreadRead (); //This makes better use of caching + return $unreadRead['unread']; } - public function countNotReadByFeed ($id) { + /*public function countNotReadByFeed ($id) { //Is this used? $sql = 'SELECT COUNT(*) AS count FROM ' . $this->prefix . 'entry WHERE is_read = 0 AND id_feed = ?'; $stm = $this->bd->prepare ($sql); $stm->execute (array ($id)); $res = $stm->fetchAll (PDO::FETCH_ASSOC); return $res[0]['count']; - } + }*/ - public function countNotReadByCat ($id) { + /*public function countNotReadByCat ($id) { //Is this used? $sql = 'SELECT COUNT(*) AS count FROM ' . $this->prefix . 'entry e INNER JOIN ' . $this->prefix . 'feed f ON e.id_feed = f.id WHERE is_read=0 AND category = ?'; $stm = $this->bd->prepare ($sql); $stm->execute (array ($id)); $res = $stm->fetchAll (PDO::FETCH_ASSOC); return $res[0]['count']; - } + }*/ - public function countNotReadFavorites () { - $sql = 'SELECT COUNT(*) AS count FROM ' . $this->prefix . 'entry WHERE is_read=0 AND is_favorite=1'; + public function countUnreadReadFavorites () { + $sql = 'SELECT is_read, COUNT(*) AS count FROM ' . $this->prefix . 'entry WHERE is_favorite=1 GROUP BY is_read'; $stm = $this->bd->prepare ($sql); $stm->execute (); $res = $stm->fetchAll (PDO::FETCH_ASSOC); - - return $res[0]['count']; + $readUnread = array('unread' => 0, 'read' => 0); + foreach ($res as $line) { + switch (intval($line['is_read'])) { + case 0: $readUnread['unread'] = intval($line['count']); break; + case 1: $readUnread['read'] = intval($line['count']); break; + } + } + return $readUnread; } - public function countFavorites () { - $sql = 'SELECT COUNT(*) AS count FROM ' . $this->prefix . 'entry WHERE is_favorite=1'; - $stm = $this->bd->prepare ($sql); - $stm->execute (); - $res = $stm->fetchAll (PDO::FETCH_ASSOC); - - return $res[0]['count']; + /*public function countNotReadFavorites () { //Is this used? //Deprecated: use countUnreadReadFavorites() instead + $unreadRead = $this->countUnreadReadFavorites (); //This makes better use of caching + return $unreadRead['unread']; + }*/ + public function countFavorites () { //Deprecated: use countUnreadReadFavorites() instead + $unreadRead = $this->countUnreadReadFavorites (); //This makes better use of caching + return $unreadRead['unread'] + $unreadRead['read']; } public function optimizeTable() { @@ -505,7 +526,7 @@ class HelperEntry { $list[$key] = self::createEntry ($dao); $count++; - $first_is_found = true; + $first_is_found = true; //TODO: Update: Now done in SQL } if ($count >= self::$nb) { $break_after = true; -- cgit v1.2.3 From f55ae730e6477f37566722961ca7e7ab9ad7f3c5 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Tue, 3 Sep 2013 23:35:33 +0200 Subject: Disable SQL LIMIT optimisation during search This patch is to make search work again after the new SQL optimisations, by removing some of the optimisations when searching is used. Optimisation of search is left for some future work. The whole base is indeed transfered from MySQL to PHP, which is not good. --- app/models/EntriesGetter.php | 12 +++++++----- app/models/Entry.php | 12 ++++++------ 2 files changed, 13 insertions(+), 11 deletions(-) (limited to 'app/models/Entry.php') diff --git a/app/models/EntriesGetter.php b/app/models/EntriesGetter.php index dc7ecca7c..803aad732 100644 --- a/app/models/EntriesGetter.php +++ b/app/models/EntriesGetter.php @@ -98,13 +98,15 @@ class EntriesGetter { HelperEntry::$first = $this->first; //TODO: Update: Now done in SQL HelperEntry::$filter = $this->filter; + $sqlLimit = (empty ($this->filter['words']) && empty ($this->filter['tags'])) ? $this->nb : ''; //Disable SQL LIMIT optimisation during search //TODO: Do better! + switch ($this->type['type']) { case 'all': list ($this->entries, $this->next) = $entryDAO->listEntries ( $this->state, $this->order, $this->first, - $this->nb + $sqlLimit ); break; case 'favoris': @@ -112,7 +114,7 @@ class EntriesGetter { $this->state, $this->order, $this->first, - $this->nb + $sqlLimit ); break; case 'public': @@ -120,7 +122,7 @@ class EntriesGetter { $this->state, $this->order, $this->first, - $this->nb + $sqlLimit ); break; case 'c': @@ -129,7 +131,7 @@ class EntriesGetter { $this->state, $this->order, $this->first, - $this->nb + $sqlLimit ); break; case 'f': @@ -138,7 +140,7 @@ class EntriesGetter { $this->state, $this->order, $this->first, - $this->nb + $sqlLimit ); break; default: diff --git a/app/models/Entry.php b/app/models/Entry.php index 4043e8f01..53f09b9c9 100755 --- a/app/models/Entry.php +++ b/app/models/Entry.php @@ -396,7 +396,7 @@ class EntryDAO extends Model_pdo { . ' ORDER BY date' . $order . ', id' . $order; if (!empty($limitCount)) { - $sql .= ' LIMIT ' . $limitCount; //TODO: See http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ + $sql .= ' LIMIT ' . ($limitCount + 1); //TODO: See http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ } $stm = $this->bd->prepare ($sql); @@ -405,19 +405,19 @@ class EntryDAO extends Model_pdo { return HelperEntry::daoToEntry ($stm->fetchAll (PDO::FETCH_ASSOC)); } public function listEntries ($state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') { - return $this->listWhere (' WHERE priority > 0', $state, $order, $limitFromId, $limitCount + 1); + return $this->listWhere (' WHERE priority > 0', $state, $order, $limitFromId, $limitCount); } public function listFavorites ($state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') { - return $this->listWhere (' WHERE is_favorite = 1', $state, $order, $limitFromId, $limitCount + 1); + return $this->listWhere (' WHERE is_favorite = 1', $state, $order, $limitFromId, $limitCount); } public function listPublic ($state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') { - return $this->listWhere (' WHERE is_public = 1', $state, $order, $limitFromId, $limitCount + 1); + return $this->listWhere (' WHERE is_public = 1', $state, $order, $limitFromId, $limitCount); } public function listByCategory ($cat, $state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') { - return $this->listWhere (' WHERE category = ?', $state, $order, $limitFromId, $limitCount + 1, array ($cat)); + return $this->listWhere (' WHERE category = ?', $state, $order, $limitFromId, $limitCount, array ($cat)); } public function listByFeed ($feed, $state, $order = 'high_to_low', $limitFromId = '', $limitCount = '') { - return $this->listWhere (' WHERE id_feed = ?', $state, $order, $limitFromId, $limitCount + 1, array ($feed)); + return $this->listWhere (' WHERE id_feed = ?', $state, $order, $limitFromId, $limitCount, array ($feed)); } public function countUnreadRead () { -- cgit v1.2.3 From 040e72fe4f4d6105239dc21d68e97dc8be724dc3 Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Wed, 4 Sep 2013 02:20:02 +0200 Subject: Work around In the current SQL request with LIMIT, if many dates are identical, the pagination may not work properly. Added a little more tolerance, but will have to be solved better. --- app/models/Entry.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'app/models/Entry.php') diff --git a/app/models/Entry.php b/app/models/Entry.php index 53f09b9c9..a763feca0 100755 --- a/app/models/Entry.php +++ b/app/models/Entry.php @@ -396,7 +396,7 @@ class EntryDAO extends Model_pdo { . ' ORDER BY date' . $order . ', id' . $order; if (!empty($limitCount)) { - $sql .= ' LIMIT ' . ($limitCount + 1); //TODO: See http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ + $sql .= ' LIMIT ' . ($limitCount + 2); //TODO: See http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ } $stm = $this->bd->prepare ($sql); -- cgit v1.2.3 From cf62bcd3d31a801a16a06608b6a953613f68fbde Mon Sep 17 00:00:00 2001 From: Alexandre Alapetite Date: Wed, 4 Sep 2013 09:28:27 +0200 Subject: Handle paging for entries with identical date Paging now works even when many entries have the same date. SQL speed could probably be improved by testing first on date, and then on CONCAT. Also, having an index on date would probably help too. --- app/models/Entry.php | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'app/models/Entry.php') diff --git a/app/models/Entry.php b/app/models/Entry.php index a763feca0..a27cce148 100755 --- a/app/models/Entry.php +++ b/app/models/Entry.php @@ -381,8 +381,8 @@ class EntryDAO extends Model_pdo { } elseif ($state == 'read') { $where .= ' AND is_read = 1'; } - if (!empty($limitFromId)) { - $where .= ' AND date ' . ($order === 'low_to_high' ? '<=' : '>=') . ' (SELECT date from freshrss_entry WHERE id = "' . $limitFromId . '")'; + if (!empty($limitFromId)) { //TODO: Consider using LPAD(e.date, 11) //CONCAT is for cases when many entries have the same date + $where .= ' AND CONCAT(e.date, e.id) ' . ($order === 'low_to_high' ? '<=' : '>=') . ' (SELECT CONCAT(s.date, s.id) from freshrss_entry s WHERE s.id = "' . $limitFromId . '")'; } if ($order == 'low_to_high') { @@ -393,7 +393,7 @@ class EntryDAO extends Model_pdo { $sql = 'SELECT e.* FROM ' . $this->prefix . 'entry e' . ' INNER JOIN ' . $this->prefix . 'feed f ON e.id_feed = f.id' . $where - . ' ORDER BY date' . $order . ', id' . $order; + . ' ORDER BY e.date' . $order . ', e.id' . $order; if (!empty($limitCount)) { $sql .= ' LIMIT ' . ($limitCount + 2); //TODO: See http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ -- cgit v1.2.3 From d9975d86a2d159c0f2442b9ee02f0523b8577af3 Mon Sep 17 00:00:00 2001 From: Marien Fressinaud Date: Sat, 14 Sep 2013 21:59:54 +0200 Subject: Issue #155 : suppression fonctions commentées MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- app/models/Entry.php | 23 +---------------------- 1 file changed, 1 insertion(+), 22 deletions(-) (limited to 'app/models/Entry.php') diff --git a/app/models/Entry.php b/app/models/Entry.php index 63e573722..d73ce7245 100755 --- a/app/models/Entry.php +++ b/app/models/Entry.php @@ -461,24 +461,6 @@ class EntryDAO extends Model_pdo { return $unreadRead['unread']; } - /*public function countNotReadByFeed ($id) { //Is this used? - $sql = 'SELECT COUNT(*) AS count FROM ' . $this->prefix . 'entry WHERE is_read = 0 AND id_feed = ?'; - $stm = $this->bd->prepare ($sql); - $stm->execute (array ($id)); - $res = $stm->fetchAll (PDO::FETCH_ASSOC); - - return $res[0]['count']; - }*/ - - /*public function countNotReadByCat ($id) { //Is this used? - $sql = 'SELECT COUNT(*) AS count FROM ' . $this->prefix . 'entry e INNER JOIN ' . $this->prefix . 'feed f ON e.id_feed = f.id WHERE is_read=0 AND category = ?'; - $stm = $this->bd->prepare ($sql); - $stm->execute (array ($id)); - $res = $stm->fetchAll (PDO::FETCH_ASSOC); - - return $res[0]['count']; - }*/ - public function countUnreadReadFavorites () { $sql = 'SELECT is_read, COUNT(*) AS count FROM ' . $this->prefix . 'entry WHERE is_favorite=1 GROUP BY is_read'; $stm = $this->bd->prepare ($sql); @@ -493,10 +475,7 @@ class EntryDAO extends Model_pdo { } return $readUnread; } - /*public function countNotReadFavorites () { //Is this used? //Deprecated: use countUnreadReadFavorites() instead - $unreadRead = $this->countUnreadReadFavorites (); //This makes better use of caching - return $unreadRead['unread']; - }*/ + public function countFavorites () { //Deprecated: use countUnreadReadFavorites() instead $unreadRead = $this->countUnreadReadFavorites (); //This makes better use of caching return $unreadRead['unread'] + $unreadRead['read']; -- cgit v1.2.3