[Labs-l] Database Indexing Doesn't seem to be working

[Brad Jorsch (Anomie)]

On Sun, Nov 9, 2014 at 1:25 PM, Maximilian Doerr <maximilian.doerr at gmail.com
> wrote:

>                             [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
>                                 )
>
>
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.

And when I try the "SHOW EXPLAIN" trick to get an explanation, it claims
it's using an index:

+------+-------------+----------+--------+---------------------------------------+----------------+---------+--------------------------+-------+-----------------------------+
| id   | select_type | table    | type   |
possible_keys                         | key            | key_len |
ref                      | rows  | Extra                       |
+------+-------------+----------+--------+---------------------------------------+----------------+---------+--------------------------+-------+-----------------------------+
|    1 | SIMPLE      | revision | ref    |
PRIMARY,page_timestamp,user_timestamp | user_timestamp | 4       |
const                    | 29186 | Using where; Using filesort |
|    1 | SIMPLE      | page     | eq_ref |
PRIMARY                               | PRIMARY        | 4       |
enwiki.revision.rev_page |     1 |                             |
+------+-------------+----------+--------+---------------------------------------+----------------+---------+--------------------------+-------+-----------------------------+

The filesort there seems odd to me, and can lead to slow queries.

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.wikimedia.org/pipermail/labs-l/attachments/20141112/424e42a6/attachment-0001.html>