<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class="">My knowledge of MySQL is pathetic to be honest. It’s definitely something I should familiarize myself with. Could you explain that to me so a pickle could understand it? :/<div class=""><br class=""><div class="">
<div style="color: rgb(0, 0, 0); letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class="">Cyberpower678<br class="">English Wikipedia Account Creation Team<br class="">Mailing List Moderator</div><div style="color: rgb(0, 0, 0); letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><br class=""></div><br class="Apple-interchange-newline">
</div>
<br class=""><div><blockquote type="cite" class=""><div class="">On Nov 12, 2014, at 10:45, Brad Jorsch (Anomie) <<a href="mailto:bjorsch@wikimedia.org" class="">bjorsch@wikimedia.org</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div dir="ltr" class=""><div class=""><div class="">On Sun, Nov 9, 2014 at 1:25 PM, Maximilian Doerr <span dir="ltr" class=""><<a href="mailto:maximilian.doerr@gmail.com" target="_blank" class="">maximilian.doerr@gmail.com</a>></span> wrote:<br class=""><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" class=""><div class=""><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)" class=""> [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 class=""></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 class=""><br class=""></div>And when I try the "SHOW EXPLAIN" trick to get an explanation, it claims it's using an index:<br class=""><br class=""><span style="font-family:courier new,monospace" class="">+------+-------------+----------+--------+---------------------------------------+----------------+---------+--------------------------+-------+-----------------------------+<br class="">| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |<br class="">+------+-------------+----------+--------+---------------------------------------+----------------+---------+--------------------------+-------+-----------------------------+<br class="">| 1 | SIMPLE | revision | ref | PRIMARY,page_timestamp,user_timestamp | user_timestamp | 4 | const | 29186 | Using where; Using filesort |<br class="">| 1 | SIMPLE | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.revision.rev_page | 1 | |<br class="">+------+-------------+----------+--------+---------------------------------------+----------------+---------+--------------------------+-------+-----------------------------+<br class=""></span><br class=""></div>The filesort there seems odd to me, and can lead to slow queries.<br class=""><br class="">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 class=""></div>
_______________________________________________<br class="">Labs-l mailing list<br class=""><a href="mailto:Labs-l@lists.wikimedia.org" class="">Labs-l@lists.wikimedia.org</a><br class="">https://lists.wikimedia.org/mailman/listinfo/labs-l<br class=""></div></blockquote></div><br class=""></div></body></html>