From 53dd2583fcf5d026f682b0966e87dbdd35fc19a8 Mon Sep 17 00:00:00 2001
From: Brion Vibber <brion@pobox.com>
Date: Fri, 17 Dec 2010 12:47:50 -0800
Subject: Switch public timeline to new sorting; new index
 notice_created_id_is_local_idx

http://status.net/wiki/Sorting_changes
---
 db/096to097.sql  | 3 +++
 db/statusnet.sql | 6 ++++++
 2 files changed, 9 insertions(+)
 create mode 100644 db/096to097.sql

diff --git a/db/096to097.sql b/db/096to097.sql
new file mode 100644
index 000000000..53f4e97c9
--- /dev/null
+++ b/db/096to097.sql
@@ -0,0 +1,3 @@
+-- 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);
diff --git a/db/statusnet.sql b/db/statusnet.sql
index ac48e6253..b372305d0 100644
--- a/db/statusnet.sql
+++ b/db/statusnet.sql
@@ -131,7 +131,13 @@ create table notice (
     location_ns integer comment 'namespace for location',
     repeat_of integer comment 'notice this is a repeat of' references notice (id),
 
+    -- For public timeline...
+    index notice_created_id_is_local_idx (created,id,is_local),
+
+    -- For profile timelines...
     index notice_profile_id_idx (profile_id,created,id),
+
+    -- Are these enough?
     index notice_conversation_idx (conversation),
     index notice_created_idx (created),
     index notice_replyto_idx (reply_to),
-- 
cgit v1.2.3