Update sorting for conversation views: adds notice_conversation_created_id_idx index on notice, replacing more limited notice_conversation_idx

3 files changed, 13 insertions, 14 deletions

diff --git a/classes/Notice.php b/classes/Notice.php
index ea69a5bed..629b7089d 100644
--- a/classes/Notice.php
+++ b/classes/Notice.php
@@ -704,19 +704,14 @@ class Notice extends Memcached_DataObject
 
         $notice->conversation = $id;
 
-        $notice->orderBy('id DESC');
+        $notice->orderBy('created DESC, id DESC');
 
         if (!is_null($offset)) {
             $notice->limit($offset, $limit);
         }
 
-        if ($since_id != 0) {
-            $notice->whereAdd('id > ' . $since_id);
-        }
-
-        if ($max_id != 0) {
-            $notice->whereAdd('id <= ' . $max_id);
-        }
+        Notice::addWhereSinceId($notice, $since_id);
+        Notice::addWhereMaxId($notice, $max_id);
 
         $ids = array();
 
diff --git a/db/096to097.sql b/db/096to097.sql
index 5947538da..88cbea457 100644
--- a/db/096to097.sql
+++ b/db/096to097.sql
@@ -1,10 +1,14 @@
 -- Add indexes for sorting changes in 0.9.7
 
--- Allows sorting public timeline and api/statuses/repeats by timestamp efficiently
+-- Allows sorting public timeline, api/statuses/repeats, and conversations by timestamp efficiently
 alter table notice
     add index notice_created_id_is_local_idx (created,id,is_local),
+
+    add index notice_repeat_of_created_id_idx (repeat_of, created, id),
     drop index notice_repeatof_idx,
-    add index notice_repeat_of_created_id_idx (repeat_of, created, id);
+
+    add index notice_conversation_created_id_idx (conversation, created, id),
+    drop index notice_conversation_idx;
 
 -- 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 9624edd6f..8b38f9ffe 100644
--- a/db/statusnet.sql
+++ b/db/statusnet.sql
@@ -140,11 +140,11 @@ create table notice (
     -- For api/statuses/repeats...
     index notice_repeat_of_created_id_idx (repeat_of, created, id),
 
-    -- Are these enough?
-    index notice_conversation_idx (conversation),
-    index notice_created_idx (created),
+    -- For conversation views
+    index notice_conversation_created_id_idx (conversation, created, id),
+
+    -- Are these needed/used?
     index notice_replyto_idx (reply_to),
-    index notice_repeatof_idx (repeat_of),
 
     FULLTEXT(content)
 ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;