https://bugzilla.wikimedia.org/show_bug.cgi?id=35349
--- Comment #15 from Roan Kattouw <roan.katt...@gmail.com> 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
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
