summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorBrion Vibber <brion@pobox.com>2009-12-22 20:18:27 -0800
committerBrion Vibber <brion@pobox.com>2009-12-22 20:18:27 -0800
commiteab6d1c95450cf9b209a0961ac325f2f9ce87d80 (patch)
treebb492398df10c3bc7a06ef0e9e73d258287337d5
parent38877a49224dca841d548f5d43c0143dafb19050 (diff)
Fix for massively slow friends timeline query due to indexing bug introduced with repeats.
Sorting on notice.id when our primary selector was notice_inbox.user_id caused a filesort and table scan of the notice table. Switchng to notice_inbox's notice_id means we can use our index, and everything comes right up. Before: mysql> explain SELECT notice.id AS id FROM notice JOIN notice_inbox ON notice.id = notice_inbox.notice_id WHERE notice_inbox.user_id = 18574 AND notice.repeat_of IS NULL ORDER BY notice.id DESC LIMIT 61 OFFSET 0; +----+-------------+--------------+--------+------------------------------------+---------+---------+-------------------------------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+--------+------------------------------------+---------+---------+-------------------------------+--------+----------------------------------------------+ | 1 | SIMPLE | notice_inbox | ref | PRIMARY,notice_inbox_notice_id_idx | PRIMARY | 4 | const | 102600 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | notice | eq_ref | PRIMARY | PRIMARY | 4 | stoica.notice_inbox.notice_id | 1 | Using index | +----+-------------+--------------+--------+------------------------------------+---------+---------+-------------------------------+--------+----------------------------------------------+ After: mysql> explain SELECT notice.id AS id FROM notice JOIN notice_inbox ON notice.id = notice_inbox.notice_id WHERE notice_inbox.user_id = 18574 AND notice.repeat_of IS NULL ORDER BY notice_id DESC LIMIT 61 OFFSET 0; +----+-------------+--------------+--------+------------------------------------+---------+---------+-------------------------------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+--------+------------------------------------+---------+---------+-------------------------------+--------+--------------------------+ | 1 | SIMPLE | notice_inbox | ref | PRIMARY,notice_inbox_notice_id_idx | PRIMARY | 4 | const | 102816 | Using where; Using index | | 1 | SIMPLE | notice | eq_ref | PRIMARY,notice_repeatof_idx | PRIMARY | 4 | stoica.notice_inbox.notice_id | 1 | Using where | +----+-------------+--------------+--------+------------------------------------+---------+---------+-------------------------------+--------+--------------------------+
-rw-r--r--classes/User.php2
1 files changed, 1 insertions, 1 deletions
diff --git a/classes/User.php b/classes/User.php
index ae709b46b..484dc8c82 100644
--- a/classes/User.php
+++ b/classes/User.php
@@ -543,7 +543,7 @@ class User extends Memcached_DataObject
// NOTE: we sort by fave time, not by notice time!
- $qry .= 'ORDER BY notice.id DESC ';
+ $qry .= 'ORDER BY notice_id DESC ';
if (!is_null($offset)) {
$qry .= "LIMIT $limit OFFSET $offset";