Update sorting for tag-filtered public timeline: needs notice_tag_tag_created_notice_id_idx index added to notice_tag

3 files changed, 11 insertions, 9 deletions

diff --git a/classes/Notice_tag.php b/classes/Notice_tag.php
index 6eada7022..bb67c8f81 100644
--- a/classes/Notice_tag.php
+++ b/classes/Notice_tag.php
@@ -55,15 +55,10 @@ class Notice_tag extends Memcached_DataObject
         $nt->selectAdd();
         $nt->selectAdd('notice_id');
 
-        if ($since_id != 0) {
-            $nt->whereAdd('notice_id > ' . $since_id);
-        }
-
-        if ($max_id != 0) {
-            $nt->whereAdd('notice_id <= ' . $max_id);
-        }
+        Notice::addWhereSinceId($nt, $since_id, 'notice_id');
+        Notice::addWhereMaxId($nt, $max_id, 'notice_id');
 
-        $nt->orderBy('notice_id DESC');
+        $nt->orderBy('created DESC, notice_id DESC');
 
         if (!is_null($offset)) {
             $nt->limit($offset, $limit);
diff --git a/db/096to097.sql b/db/096to097.sql
index 53f4e97c9..38e4e958b 100644
--- a/db/096to097.sql
+++ b/db/096to097.sql
@@ -1,3 +1,7 @@
 -- Add indexes for sorting changes in 0.9.7
+
 -- Allows sorting public timeline by timestamp efficiently
 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);
diff --git a/db/statusnet.sql b/db/statusnet.sql
index b372305d0..76a821baf 100644
--- a/db/statusnet.sql
+++ b/db/statusnet.sql
@@ -307,7 +307,10 @@ create table notice_tag (
 
     constraint primary key (tag, notice_id),
     index notice_tag_created_idx (created),
-    index notice_tag_notice_id_idx (notice_id)
+    index notice_tag_notice_id_idx (notice_id),
+
+    -- For sorting tag-filtered public timeline
+    index notice_tag_tag_created_notice_id_idx (tag, created, notice_id)
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
 /* Synching with foreign services */