<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="">Thanks for the clarification, but what I don’t understand is why it’s so slow now when it used take a few seconds at most to fetch the data for this query.<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 13, 2014, at 14:00, 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="">On Wed, Nov 12, 2014 at 11:06 AM, 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="">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></blockquote><div class=""><br class=""></div><div class="">Unfortunately, I don't speak pickle. But I'll try to clarify.<span class=""><br class=""><br class=""></span><br class="">"enwiki_p.revision_userindex" is a view (along with everything else in enwiki_p, I believe), which is basically a pretend
table that's really populated by a SELECT query (in this case, from
"enwiki.revision"). You can use "SHOW CREATE TABLE
enwiki_p.revision_userindex;" to see that query if you want. So when you
select from the view, it basically rewrites your query to incorporate
that other select.<br class=""><div class=""><blockquote class="gmail_quote" type="cite"><div class=""><div class=""><div class="h5"><div dir="ltr" class=""><div 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=""></span></div></div></div></div></div></blockquote><div class="">The "key" column here tells us that it is in fact using the user_timestamp index to find the rows in the revision table. Which is good, indexing isn't broken.<br class=""><br class=""></div><div class="">The "Extra" column tells us a few things.<br class=""><ul class=""><li class="">It doesn't have "Using index", so it's having to actually fetch the row from the table instead of just getting what it needs from the index (in this case, that's because the view filters based on rev_deleted). That makes things slightly slower, especially if you're having to hit cold pages (i.e. blocks from the disk that aren't already cached in memory).<br class=""></li><li class="">"Using where" tells us that it might to throw away some of the rows that it fetched, thanks to a WHERE clause (again, the rev_deleted check).</li><li class="">"Using filesort" means that it's going to load all the rows into memory (or worse, write them to a temp file) and then sort them to put them in ORDER BY order. This can get really slow if there are a lot of them. I don't know why it's deciding it needs to do this here, since the user_timestamp index should already be giving the rows in the asked-for order.<br class=""></li></ul></div></div></div></div>And then, of course, it's having to fetch rows from the page table as well, which could again be hitting cold pages.<br class=""></div><div class="gmail_extra"><br clear="all" class=""><br class="">-- <br class=""><div class="gmail_signature">Brad Jorsch (Anomie)<br class="">Software Engineer<br class="">Wikimedia Foundation</div>
</div></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>