https://bugzilla.wikimedia.org/show_bug.cgi?id=35349
--- Comment #15 from Roan Kattouw <[email protected]> 2012-03-29 10:34:43 UTC --- (In reply to comment #10) > (In reply to comment #3) > > Hi, thanks for your interest! > > The output of the explain select is exactly the same if I run with or > > without > > "mixed", are you sure it is the correct test for checking performance ? > > > > id select_type table type possible_keys key key_len > > ref > > rows Extra > > 1 SIMPLE page ALL PRIMARY NULL NULL NULL 1 Using > > temporary; Using filesort > > 1 SIMPLE revision range usertext_timestamp > > usertext_timestamp > > 257 NULL 2 Using where; Using join buffer > > > > (and 257 becomes 271 if there is a uccontinue condition) > > > Yes, EXPLAIN is normally used to get an indication of the database > performance. > From the indices on the revision table it looks like this should work, but I > think somebody who knows more about query performance should have a look at > this. Both "ALL" and "Using filesort" are red flags. Here are my explains from the toolserver: mysql> explain select page_namespace, page_title, rev_id, rev_timestamp from page, revision where page_id=rev_page and rev_user_text LIKE 'J%' order by rev_user_text, rev_timestamp limit 51 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: revision type: range possible_keys: PRIMARY,page_timestamp,usertext_timestamp key: usertext_timestamp key_len: 257 ref: NULL rows: 46784760 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: page type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: enwiki.revision.rev_page rows: 1 Extra: 2 rows in set (0.00 sec) mysql> explain select page_namespace, page_title, rev_id, rev_timestamp from page, revision where page_id=rev_page and rev_user_text LIKE 'J%' order by rev_timestamp, rev_user_text limit 51 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: revision type: range possible_keys: PRIMARY,page_timestamp,usertext_timestamp key: usertext_timestamp key_len: 257 ref: NULL rows: 46784760 Extra: Using where; Using index; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: page type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: enwiki.revision.rev_page rows: 1 Extra: 2 rows in set (0.00 sec) Here's what happened when I ran these queries on the toolserver: mysql> select page_namespace, page_title, rev_id, rev_timestamp from page, revision where page_id=rev_page and rev_user_text LIKE 'J%' order by rev_user_text, rev_timestamp limit 51; [...] 51 rows in set (0.00 sec) mysql> select page_namespace, page_title, rev_id, rev_timestamp from page, revision where page_id=rev_page and rev_user_text LIKE 'J%' order by rev_timestamp, rev_user_text limit 51; [after about a minute] ^CCtrl-C -- sending "KILL QUERY 9922979" to server ... Ctrl-C -- query aborted. ERROR 1028 (HY000): Sort aborted This makes sense when you consider how MySQL is executing this query: it fetches all rows whose user starts with a J (according to EXPLAIN there are approximately 46 million of these), then sorts those by (timestamp,user) using a slow filesort (unindexed sort). So yes, this patch has major performance implications. An API query like ucuserprefix=J&ucsort=timestamp would result in an SQL query that takes over a minute to execute. -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the assignee for the bug. You are on the CC list for the bug. _______________________________________________ Wikibugs-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
