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.
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              |
+----+-------------+--------------+--------+------------------------------------+---------+---------+-------------------------------+--------+--------------------------+

znarf/statusnet-ladistribution,brion/StatusNet,faulteh/gnu-social,sikevux/bsk-social,znarf/statusnet-ladistribution,Piratas/tortuga,xebia/statusnet-oauth2,Piratas/tortuga,chimo/social,chimo/social,gnusocial/mainline,faulteh/gnu-social,minmb/statusnet,minmb/statusnet,UngPirat/freesocial,shashi/StatusNet,charlycoste/StatusNet,studio666/gnu-social,furtherfield/gnu-social,zh/statusnet,sikevux/bsk-social,csarven/statusnet,d3vgru/StatusNet,xebia/statusnet-oauth2,d3vgru/StatusNet,d3vgru/StatusNet,studio666/gnu-social,eXcomm/gnu-social,znarf/statusnet-ladistribution,charnimda/lgbtpzn-social,brion/StatusNet,gnusocial/mainline,furtherfield/gnu-social,faulteh/gnu-social,foocorp/gnu-social,faulteh/gnu-social,bankonme/gnu-social,zcopley/StatusNet,Piratas/tortuga,zcopley/StatusNet,UngPirat/freesocial,shashi/StatusNet,foocorp/gnu-social,xebia/statusnet-oauth2,UngPirat/freesocial,furtherfield/gnu-social,foocorp/gnu-social,charnimda/lgbtpzn-social,furtherfield/gnu-social,eXcomm/gnu-social,zcopley/StatusNet,bankonme/gnu-social,csarven/statusnet,studio666/gnu-social,charnimda/lgbtpzn-social,charlycoste/StatusNet,zh/statusnet,sikevux/bsk-social,brion/StatusNet,eXcomm/gnu-social,csarven/statusnet,zcopley/zcopleys-statusnet-clone,bankonme/gnu-social,eXcomm/gnu-social,zh/statusnet,Piratas/tortuga,charlycoste/StatusNet,gnusocial/mainline,csarven/statusnet,chimo/social,minmb/statusnet,studio666/gnu-social,zcopley/zcopleys-statusnet-clone,bankonme/gnu-social,shashi/StatusNet,sikevux/bsk-social,gnusocial/mainline,zcopley/zcopleys-statusnet-clone