From 53dd2583fcf5d026f682b0966e87dbdd35fc19a8 Mon Sep 17 00:00:00 2001 From: Brion Vibber 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 (limited to 'db') 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-54-g00ecf From 00a5a5342ab0c44d59697cded81f6e96ba49c42c Mon Sep 17 00:00:00 2001 From: Brion Vibber Date: Fri, 17 Dec 2010 14:37:46 -0800 Subject: Update sorting for tag-filtered public timeline: needs notice_tag_tag_created_notice_id_idx index added to notice_tag --- classes/Notice_tag.php | 11 +++-------- db/096to097.sql | 4 ++++ db/statusnet.sql | 5 ++++- 3 files changed, 11 insertions(+), 9 deletions(-) (limited to 'db') 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 */ -- cgit v1.2.3-54-g00ecf 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(-) (limited to 'db') 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-54-g00ecf From 66474586af58c4e505117f0fed7382831ec008b1 Mon Sep 17 00:00:00 2001 From: Brion Vibber Date: Fri, 17 Dec 2010 14:51:37 -0800 Subject: Update sorting for group inbox timelines; adds group_inbox_group_id_created_notice_id_idx index to group_inbox table --- classes/User_group.php | 11 +++-------- db/096to097.sql | 3 +++ db/statusnet.sql | 5 ++++- 3 files changed, 10 insertions(+), 9 deletions(-) (limited to 'db') diff --git a/classes/User_group.php b/classes/User_group.php index 60217e960..cffc78645 100644 --- a/classes/User_group.php +++ b/classes/User_group.php @@ -100,15 +100,10 @@ class User_group extends Memcached_DataObject $inbox->selectAdd(); $inbox->selectAdd('notice_id'); - if ($since_id != 0) { - $inbox->whereAdd('notice_id > ' . $since_id); - } - - if ($max_id != 0) { - $inbox->whereAdd('notice_id <= ' . $max_id); - } + Notice::addWhereSinceId($inbox, $since_id, 'notice_id'); + Notice::addWhereMaxId($inbox, $max_id, 'notice_id'); - $inbox->orderBy('notice_id DESC'); + $inbox->orderBy('created DESC, notice_id DESC'); if (!is_null($offset)) { $inbox->limit($offset, $limit); diff --git a/db/096to097.sql b/db/096to097.sql index 4171e9589..c3f1fb425 100644 --- a/db/096to097.sql +++ b/db/096to097.sql @@ -8,3 +8,6 @@ alter table notice_tag add index notice_tag_tag_created_notice_id_idx (tag, crea -- Needed for sorting reply/mentions timelines alter table reply add index reply_profile_id_modified_notice_id_idx (profile_id, modified, notice_id); + +-- Needed for sorting group messages by timestamp +alter table group_inbox add index group_inbox_group_id_created_notice_id_idx (group_id, created, notice_id); diff --git a/db/statusnet.sql b/db/statusnet.sql index dfc46f79e..5898abf46 100644 --- a/db/statusnet.sql +++ b/db/statusnet.sql @@ -480,7 +480,10 @@ create table group_inbox ( 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; -- cgit v1.2.3-54-g00ecf From 1b90ed564a19dcb3d24d0c0620ce0623773fe4d0 Mon Sep 17 00:00:00 2001 From: Brion Vibber Date: Fri, 17 Dec 2010 15:13:09 -0800 Subject: Update sorting on api/statuses/retweets: adds notice_repeat_of_created_id_idx index to replace notice_repeatof_idx --- classes/Notice.php | 2 +- db/096to097.sql | 7 +++++-- db/statusnet.sql | 4 ++++ 3 files changed, 10 insertions(+), 3 deletions(-) (limited to 'db') diff --git a/classes/Notice.php b/classes/Notice.php index c58705c4b..ea69a5bed 100644 --- a/classes/Notice.php +++ b/classes/Notice.php @@ -1690,7 +1690,7 @@ class Notice extends Memcached_DataObject $notice->repeat_of = $this->id; - $notice->orderBy('created'); // NB: asc! + $notice->orderBy('created, id'); // NB: asc! if (!is_null($limit)) { $notice->limit(0, $limit); diff --git a/db/096to097.sql b/db/096to097.sql index c3f1fb425..5947538da 100644 --- a/db/096to097.sql +++ b/db/096to097.sql @@ -1,7 +1,10 @@ -- 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 public timeline and api/statuses/repeats by timestamp efficiently +alter table notice + add index notice_created_id_is_local_idx (created,id,is_local), + drop index notice_repeatof_idx, + add index notice_repeat_of_created_id_idx (repeat_of, created, id); -- 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 5898abf46..9624edd6f 100644 --- 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 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), index notice_replyto_idx (reply_to), index notice_repeatof_idx (repeat_of), + FULLTEXT(content) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci; -- cgit v1.2.3-54-g00ecf From fb65d5901d586a13886b81d84de8959b67b6aa9e Mon Sep 17 00:00:00 2001 From: Brion Vibber Date: Fri, 17 Dec 2010 16:08:37 -0800 Subject: Update sorting for conversation views: adds notice_conversation_created_id_idx index on notice, replacing more limited notice_conversation_idx --- classes/Notice.php | 11 +++-------- db/096to097.sql | 8 ++++++-- db/statusnet.sql | 8 ++++---- 3 files changed, 13 insertions(+), 14 deletions(-) (limited to 'db') 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; -- cgit v1.2.3-54-g00ecf From 146d6b8b735472a2ae4c2f8d2653243492651399 Mon Sep 17 00:00:00 2001 From: Brion Vibber Date: Fri, 17 Dec 2010 16:12:44 -0800 Subject: Sorting index fix for role lookups: adds profile_role_role_created_profile_id_idx index on profile_role --- db/096to097.sql | 3 +++ db/statusnet.sql | 3 ++- 2 files changed, 5 insertions(+), 1 deletion(-) (limited to 'db') diff --git a/db/096to097.sql b/db/096to097.sql index 88cbea457..875077e8b 100644 --- a/db/096to097.sql +++ b/db/096to097.sql @@ -18,3 +18,6 @@ alter table reply add index reply_profile_id_modified_notice_id_idx (profile_id, -- Needed for sorting group messages by timestamp alter table group_inbox add index group_inbox_group_id_created_notice_id_idx (group_id, created, notice_id); + +-- Helps make some reverse role lookups more efficient if there's a lot of assigned accounts +alter table profile_role add index profile_role_role_created_profile_id_idx (role, created, profile_id); diff --git a/db/statusnet.sql b/db/statusnet.sql index 8b38f9ffe..0c1697a7e 100644 --- a/db/statusnet.sql +++ b/db/statusnet.sql @@ -627,7 +627,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', - 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; -- cgit v1.2.3-54-g00ecf From 5300d657cc881a1e8dba59498c90098930029d68 Mon Sep 17 00:00:00 2001 From: Brion Vibber Date: Fri, 17 Dec 2010 16:22:26 -0800 Subject: Sort indexing fix for profile sidebar: add group_member_profile_id_created_idx to group_member table, streamlines sorting of your group memberships in the sidebar --- db/096to097.sql | 3 +++ db/statusnet.sql | 5 ++++- 2 files changed, 7 insertions(+), 1 deletion(-) (limited to 'db') diff --git a/db/096to097.sql b/db/096to097.sql index 875077e8b..209a3a881 100644 --- a/db/096to097.sql +++ b/db/096to097.sql @@ -21,3 +21,6 @@ alter table group_inbox add index group_inbox_group_id_created_notice_id_idx (gr -- Helps make some reverse role lookups more efficient if there's a lot of assigned accounts alter table profile_role add index profile_role_role_created_profile_id_idx (role, created, profile_id); + +-- Fix for sorting a user's group memberships by order joined +alter table group_member add index group_member_profile_id_created_idx (profile_id, created); diff --git a/db/statusnet.sql b/db/statusnet.sql index 0c1697a7e..4a24d016a 100644 --- a/db/statusnet.sql +++ b/db/statusnet.sql @@ -463,7 +463,10 @@ create table group_member ( 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; -- cgit v1.2.3-54-g00ecf