diff options
| author | Brion Vibber <brion@pobox.com> | 2009-12-22 20:18:27 -0800 |
|---|---|---|
| committer | Brion Vibber <brion@pobox.com> | 2009-12-22 20:30:41 -0800 |
| commit | fa0fbd011812e9df52c9e5684d4cf953c6f75d8e (patch) | |
| tree | a1d7c800f68b5fd820b6f21c25eb1273ce135ab3 | |
| parent | f6bf9529805cd58fdd1671dd9b133bde05e8ae87 (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.php | 2 |
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"; |