Merge branch 'testing' into moveaccount

[quix0rs-gnu-social.git] / db / statusnet.sql

diff --git a/db/statusnet.sql b/db/statusnet.sql
index 76a821bafe513001fecb25fdc44c23373489ee57..4a24d016a286e5a2e2cdaffc7314543a5568cb64 100644 (file)
--- a/db/statusnet.sql
+++ b/db/statusnet.sql
@@ -137,11 +137,15 @@ create table notice (
     -- For profile timelines...
     index notice_profile_id_idx (profile_id,created,id),
 
     -- 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),
+    -- For api/statuses/repeats...
+    index notice_repeat_of_created_id_idx (repeat_of, created, id),
+
+    -- For conversation views
+    index notice_conversation_created_id_idx (conversation, created, id),
+
+    -- Are these needed/used?

     index notice_replyto_idx (reply_to),
     index notice_replyto_idx (reply_to),

-    index notice_repeatof_idx (repeat_of),
+

     FULLTEXT(content)
 ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
 
     FULLTEXT(content)
 ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
 


@@ -162,7 +166,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),
     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;
 
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 


@@ -456,7 +463,10 @@ create table group_member (
 
     constraint primary key (group_id, profile_id),
     index group_member_profile_id_idx (profile_id),
 
     constraint primary key (group_id, profile_id),
     index group_member_profile_id_idx (profile_id),

-    index group_member_created_idx (created)
+    index group_member_created_idx (created),
+
+    -- To pull up a list of someone's groups in order joined
+    index group_member_profile_id_created_idx (profile_id, created)

 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 


@@ -477,7 +487,10 @@ create table group_inbox (
 
     constraint primary key (group_id, notice_id),
     index group_inbox_created_idx (created),
 
     constraint primary key (group_id, notice_id),
     index group_inbox_created_idx (created),

-    index group_inbox_notice_id_idx (notice_id)
+    index group_inbox_notice_id_idx (notice_id),
+
+    -- Needed for sorting group messages by timestamp
+    index group_inbox_group_id_created_notice_id_idx (group_id, created, notice_id)

 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 


@@ -617,7 +630,8 @@ create table profile_role (
     role    varchar(32) not null comment 'string representing the role',
     created datetime not null comment 'date the role was granted',
 
     role    varchar(32) not null comment 'string representing the role',
     created datetime not null comment 'date the role was granted',
 

-    constraint primary key (profile_id, role)
+    constraint primary key (profile_id, role),
+    index profile_role_role_created_profile_id_idx (role, created, profile_id)

 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;
 
 
 ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin;