diff options
author | Bernhard Posselt <nukeawhale@gmail.com> | 2013-03-22 21:39:39 +0100 |
---|---|---|
committer | Bernhard Posselt <nukeawhale@gmail.com> | 2013-03-22 21:39:39 +0100 |
commit | f9107664b01ba6f9cb3283a08a9ae4baf6026215 (patch) | |
tree | c3d783e4de04c65ec4bbaf6d86acde5ab8199f0f /db | |
parent | 91a327f5c687014fe20b531b9a42b98fe151d017 (diff) |
made mappers faster by only joining the relevant rows for each user
Diffstat (limited to 'db')
-rw-r--r-- | db/feedmapper.php | 14 | ||||
-rw-r--r-- | db/foldermapper.php | 8 | ||||
-rw-r--r-- | db/itemmapper.php | 48 |
3 files changed, 35 insertions, 35 deletions
diff --git a/db/feedmapper.php b/db/feedmapper.php index 131eb2090..22f71d7a3 100644 --- a/db/feedmapper.php +++ b/db/feedmapper.php @@ -37,11 +37,11 @@ class FeedMapper extends Mapper implements IMapper { public function find($id, $userId){ - $sql = 'SELECT * FROM `*dbprefix*news_feeds` ' . + $sql = 'SELECT * FROM `*PREFIX*news_feeds` ' . 'WHERE `id` = ? ' . 'AND `user_id` = ?'; - $row = $this->findQuery($sql, array($id, $userId)); + $row = $this->findOneQuery($sql, array($id, $userId)); $feed = new Feed(); $feed->fromRow($row); @@ -65,8 +65,8 @@ class FeedMapper extends Mapper implements IMapper { public function findAllFromUser($userId){ $sql = 'SELECT `feeds`.*, COUNT(`items`.`id`) AS `unread_count` ' . - 'FROM `*dbprefix*news_feeds` `feeds` ' . - 'LEFT OUTER JOIN `*dbprefix*news_items` `items` ' . + 'FROM `*PREFIX*news_feeds` `feeds` ' . + 'LEFT OUTER JOIN `*PREFIX*news_items` `items` ' . 'ON `feeds`.`id` = `items`.`feed_id` ' . 'WHERE (`items`.`status` & ?) > 0 ' . 'AND `feeds`.`user_id` = ? ' . @@ -78,19 +78,19 @@ class FeedMapper extends Mapper implements IMapper { public function findAll(){ - $sql = 'SELECT * FROM `*dbprefix*news_feeds`'; + $sql = 'SELECT * FROM `*PREFIX*news_feeds`'; return $this->findAllRows($sql); } public function findByUrlHash($hash, $userId){ - $sql = 'SELECT * FROM `*dbprefix*news_feeds` ' . + $sql = 'SELECT * FROM `*PREFIX*news_feeds` ' . 'WHERE `url_hash` = ? ' . 'AND `user_id` = ?'; $params = array($hash, $userId); - $row = $this->findQuery($sql, $params); + $row = $this->findOneQuery($sql, $params); $feed = new Feed(); $feed->fromRow($row); diff --git a/db/foldermapper.php b/db/foldermapper.php index ef2f31f2d..82739df1b 100644 --- a/db/foldermapper.php +++ b/db/foldermapper.php @@ -36,11 +36,11 @@ class FolderMapper extends Mapper implements IMapper { } public function find($id, $userId){ - $sql = 'SELECT * FROM `*dbprefix*news_folders` ' . + $sql = 'SELECT * FROM `*PREFIX*news_folders` ' . 'WHERE `id` = ? ' . 'AND `user_id` = ?'; - $row = $this->findQuery($sql, array($id, $userId)); + $row = $this->findOneQuery($sql, array($id, $userId)); $folder = new Folder(); $folder->fromRow($row); @@ -63,7 +63,7 @@ class FolderMapper extends Mapper implements IMapper { public function findAllFromUser($userId){ - $sql = 'SELECT * FROM `*dbprefix*news_folders` ' . + $sql = 'SELECT * FROM `*PREFIX*news_folders` ' . 'WHERE `user_id` = ?'; $params = array($userId); @@ -72,7 +72,7 @@ class FolderMapper extends Mapper implements IMapper { public function findByName($folderName, $userId){ - $sql = 'SELECT * FROM `*dbprefix*news_folders` ' . + $sql = 'SELECT * FROM `*PREFIX*news_folders` ' . 'WHERE `name` = ?' . 'AND `user_id` = ?'; $params = array($folderName, $userId); diff --git a/db/itemmapper.php b/db/itemmapper.php index 03bc9e1cd..f7d27ab23 100644 --- a/db/itemmapper.php +++ b/db/itemmapper.php @@ -52,13 +52,13 @@ class ItemMapper extends Mapper implements IMapper { public function find($id, $userId){ - $sql = 'SELECT `*dbprefix*news_items`.* FROM `*dbprefix*news_items` ' . - 'JOIN `*dbprefix*news_feeds` ' . - 'ON `*dbprefix*news_feeds`.`id` = `*dbprefix*news_items`.`feed_id` ' . - 'WHERE `*dbprefix*news_items`.`id` = ? ' . - 'AND `*dbprefix*news_feeds`.`user_id` = ? '; + $sql = 'SELECT `*PREFIX*news_items`.* FROM `*PREFIX*news_items` ' . + 'JOIN `*PREFIX*news_feeds` ' . + 'ON `*PREFIX*news_feeds`.`id` = `*PREFIX*news_items`.`feed_id` ' . + 'AND `*PREFIX*news_feeds`.`user_id` = ? ' . + 'WHERE `*PREFIX*news_items`.`id` = ? '; - $row = $this->findQuery($sql, array($id, $userId)); + $row = $this->findOneQuery($sql, array($id, $userId)); $item = new Item(); $item->fromRow($row); @@ -68,11 +68,11 @@ class ItemMapper extends Mapper implements IMapper { public function starredCount($userId){ - $sql = 'SELECT COUNT(*) AS size FROM `*dbprefix*news_feeds` `feeds` ' . - 'JOIN `*dbprefix*news_items` `items` ' . + $sql = 'SELECT COUNT(*) AS size FROM `*PREFIX*news_feeds` `feeds` ' . + 'JOIN `*PREFIX*news_items` `items` ' . 'ON `items`.`feed_id` = `feeds`.`id` ' . - 'WHERE `feeds`.`user_id` = ? ' . - 'AND ((`items`.`status` & ?) > 0)'; + 'AND `feeds`.`user_id` = ? ' . + 'WHERE ((`items`.`status` & ?) > 0)'; $params = array($userId, StatusFlag::STARRED); @@ -83,12 +83,12 @@ class ItemMapper extends Mapper implements IMapper { public function readFeed($feedId, $userId){ - $sql = 'UPDATE `*dbprefix*news_feeds` `feeds` ' . - 'JOIN `*dbprefix*news_items` `items` ' . + $sql = 'UPDATE `*PREFIX*news_feeds` `feeds` ' . + 'JOIN `*PREFIX*news_items` `items` ' . 'ON `items`.`feed_id` = `feeds`.`id` ' . + 'AND `feeds`.`user_id` = ? ' . 'SET `items`.`status` = (`items`.`status` & ?) ' . - 'WHERE `feeds`.`user_id` = ? ' . - 'AND `items`.`id` = ?'; + 'WHERE `items`.`id` = ?'; $params = array(~StatusFlag::UNREAD, $userId, $feedId); $this->execute($sql, $params); @@ -100,12 +100,12 @@ class ItemMapper extends Mapper implements IMapper { * Queries to find all items from a folder that belongs to a user */ /*private function makeFindAllFromFolderQuery($custom) { - return 'SELECT `*dbprefix*news_items`.* FROM `*dbprefix*news_items` ' . - 'JOIN `*dbprefix*news_feeds` ' . - 'ON `*dbprefix*news_feeds`.`id` = `*dbprefix*news_items`.`feed_id` ' . - 'WHERE `*dbprefix*news_feeds`.`user_id` = ? ' . - 'AND `*dbprefix*news_feeds`.`folder_id` = ? ' . - 'AND ((`*dbprefix*news_items`.`status` & ?) > 0) ' . + return 'SELECT `*PREFIX*news_items`.* FROM `*PREFIX*news_items` ' . + 'JOIN `*PREFIX*news_feeds` ' . + 'ON `*PREFIX*news_feeds`.`id` = `*PREFIX*news_items`.`feed_id` ' . + 'WHERE `*PREFIX*news_feeds`.`user_id` = ? ' . + 'AND `*PREFIX*news_feeds`.`folder_id` = ? ' . + 'AND ((`*PREFIX*news_items`.`status` & ?) > 0) ' . $custom; } @@ -116,7 +116,7 @@ class ItemMapper extends Mapper implements IMapper { } public function findAllFromFolderByLastMofified($userId, $folderId, $status, $lastModified) { - $sql = $this->makeFindAllFromFolderQuery(' AND (`*dbprefix*news_items`.`last_modified` >= ?)'); + $sql = $this->makeFindAllFromFolderQuery(' AND (`*PREFIX*news_items`.`last_modified` >= ?)'); $params = array($userId, $folderId, $status, $lastModified); return $this->findAllRows($sql, $params); } @@ -149,19 +149,19 @@ class ItemMapper extends Mapper implements IMapper { // } // // public function findAllFromFeedByStatus($feedId, $userId, $status){ -// $sql = $this->makeFindAllFromFeedQuery(' AND ((`*dbprefix*news_items`.`status` & ?) > 0)'); +// $sql = $this->makeFindAllFromFeedQuery(' AND ((`*PREFIX*news_items`.`status` & ?) > 0)'); // $params = array($feedId, $userId, $status); // return $this->findAllRows($sql, $params); // } // // public function findAllFromFeedByLastMofified($userId, $feedId, $lastModified){ -// $sql = $this->makeFindAllFromFeedQuery(' AND `*dbprefix*news_items`.last_modified >= ? '); +// $sql = $this->makeFindAllFromFeedQuery(' AND `*PREFIX*news_items`.last_modified >= ? '); // $params = array($feedId, $userId, $lastModified); // return $this->findAllRows($sql, $params); // } // // public function findAllFromFeedByOffset($userId, $feedId, $limit, $offset){ -// $sql = $this->makeFindAllFromFeedQuery(' AND `*dbprefix*news_items`.last_modified >= ? '); +// $sql = $this->makeFindAllFromFeedQuery(' AND `*PREFIX*news_items`.last_modified >= ? '); // $params = array($feedId, $userId, $limit, $offset); // return $this->findAllRows($sql, $params); // } |