<div dir="ltr"><div><div>On Sun, Nov 9, 2014 at 1:25 PM, Maximilian Doerr <span dir="ltr"><<a href="mailto:maximilian.doerr@gmail.com" target="_blank">maximilian.doerr@gmail.com</a>></span> wrote:<br><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div style="word-wrap:break-word"><div><pre style="padding:9.5px;font-family:Monaco,Menlo,Consolas,"Courier New",monospace;font-size:13px;color:rgb(51,51,51);border-radius:4px;margin-top:0px;margin-bottom:10px;line-height:20px;word-break:break-all;word-wrap:break-word;white-space:pre-wrap;background-color:rgb(245,245,245);border:1px solid rgba(0,0,0,0.15)">                            [1] => Array
                                (
                                    [time] => 18.95
                                    [query] => SELECT rev_timestamp, page_title, page_namespace FROM revision_userindex JOIN page ON page_id = rev_page WHERE (`rev_user` = '14836860') AND `rev_timestamp` > 1 ORDER BY rev_timestamp ASC LIMIT 0,2688354;
                                    [result] => succeeded
                                )
</pre></div></div></blockquote></div><br></div>The proper indexes seem to be in place on the underlying tables, based on a "SHOW CREATE TABLE enwiki.revision" which is the backing for both enwiki_p.revision and enwiki_p.revision_usertext.<br><br></div>And when I try the "SHOW EXPLAIN" trick to get an explanation, it claims it's using an index:<br><br><span style="font-family:courier new,monospace">+------+-------------+----------+--------+---------------------------------------+----------------+---------+--------------------------+-------+-----------------------------+<br>| id   | select_type | table    | type   | possible_keys                         | key            | key_len | ref                      | rows  | Extra                       |<br>+------+-------------+----------+--------+---------------------------------------+----------------+---------+--------------------------+-------+-----------------------------+<br>|    1 | SIMPLE      | revision | ref    | PRIMARY,page_timestamp,user_timestamp | user_timestamp | 4       | const                    | 29186 | Using where; Using filesort |<br>|    1 | SIMPLE      | page     | eq_ref | PRIMARY                               | PRIMARY        | 4       | enwiki.revision.rev_page |     1 |                             |<br>+------+-------------+----------+--------+---------------------------------------+----------------+---------+--------------------------+-------+-----------------------------+<br></span><br></div>The filesort there seems odd to me, and can lead to slow queries.<br><br>Or it could just be that it's having to load 13996 rows from the revision table and 13996 rows from the page table and the disk access is slow.<br></div>