From 3ddfa4de931f4eb3083ac877898b5ee8b03a82f1 Mon Sep 17 00:00:00 2001 From: Brion Vibber Date: Fri, 17 Dec 2010 14:43:45 -0800 Subject: Update sorting on reply/mentions timeline: added reply_profile_id_modified_notice_id_idx index to reply table --- classes/Reply.php | 11 +++-------- db/096to097.sql | 3 +++ db/statusnet.sql | 5 ++++- 3 files changed, 10 insertions(+), 9 deletions(-) diff --git a/classes/Reply.php b/classes/Reply.php index da8a4f685..371c16cf4 100644 --- a/classes/Reply.php +++ b/classes/Reply.php @@ -50,15 +50,10 @@ class Reply extends Memcached_DataObject $reply = new Reply(); $reply->profile_id = $user_id; - if ($since_id != 0) { - $reply->whereAdd('notice_id > ' . $since_id); - } - - if ($max_id != 0) { - $reply->whereAdd('notice_id <= ' . $max_id); - } + Notice::addWhereSinceId($reply, $since_id, 'notice_id', 'modified'); + Notice::addWhereMaxId($reply, $max_id, 'notice_id', 'modified'); - $reply->orderBy('notice_id DESC'); + $reply->orderBy('modified DESC, notice_id DESC'); if (!is_null($offset)) { $reply->limit($offset, $limit); diff --git a/db/096to097.sql b/db/096to097.sql index 38e4e958b..4171e9589 100644 --- a/db/096to097.sql +++ b/db/096to097.sql @@ -5,3 +5,6 @@ alter table notice add index notice_created_id_is_local_idx (created,id,is_local -- Allows sorting tag-filtered public timeline by timestamp efficiently alter table notice_tag add index notice_tag_tag_created_notice_id_idx (tag, created, notice_id); + +-- Needed for sorting reply/mentions timelines +alter table reply add index reply_profile_id_modified_notice_id_idx (profile_id, modified, notice_id); diff --git a/db/statusnet.sql b/db/statusnet.sql index 76a821baf..dfc46f79e 100644 --- a/db/statusnet.sql +++ b/db/statusnet.sql @@ -162,7 +162,10 @@ create table reply ( constraint primary key (notice_id, profile_id), index reply_notice_id_idx (notice_id), index reply_profile_id_idx (profile_id), - index reply_replied_id_idx (replied_id) + index reply_replied_id_idx (replied_id), + + -- Needed for sorting reply/mentions timelines + index reply_profile_id_modified_notice_id_idx (profile_id, modified, notice_id) ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; -- cgit v1.2.3