On 10/18/07, tango(a)svn.wikimedia.org <tango(a)svn.wikimedia.org> wrote:
> Revision: 26830
> Author: tango
> Date: 2007-10-18 19:19:37 +0000 (Thu, 18 Oct 2007)
>
> Log Message:
> -----------
> Show changes to pages linked to via a redirect on Special:Recentchangeslinked
>
> . . .
> rc_new_len,
> rc_deleted
> " . ($uid ? ",wl_user" : "") . "
> - FROM $pagelinks, $recentchanges
> + FROM $pagelinks, $recentchanges, $redirect, $page
> " . ($uid ? " LEFT OUTER JOIN $watchlist ON wl_user={$uid} AND wl_title=rc_title AND wl_namespace=rc_namespace " : "") . "
> WHERE rc_timestamp > '{$cutoff}'
> {$cmq}
> - AND pl_namespace=rc_namespace
> + AND (pl_namespace=rc_namespace
> AND pl_title=rc_title
> - AND pl_from=$id
> + AND pl_from=$id)
> + OR (rd_namespace=rc_namespace
> + AND rd_title=rc_title
> + AND rd_from=page_id
> + AND page_namespace=pl_namespace
> + AND page_title=pl_title
> + AND pl_from=$id)
> $GROUPBY
> ";
> }
At first inspection, this appears to cause the query to be executed as
a Cartesian join (query run on a fairly old dump of the Simple English
Wikipedia on localhost with some random parameters).
mysql> EXPLAIN SELECT * FROM pagelinks, recentchanges, redirect, page
WHERE rc_timestamp > '20071011094400' AND (pl_namespace=rc_namespace
AND pl_title=rc_title) OR (rd_namespace=rc_namespace AND
rd_title=rc_title AND rd_from=page_id AND page_namespace=pl_namespace
AND page_title=pl_title) AND pl_from=1234;
+----+-------------+---------------+-------+------------------------------------------------+-------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+----+-------------+---------------+-------+------------------------------------------------+-------------+---------+------+---------+--------------------------+
| 1 | SIMPLE | pagelinks | index | pl_from,pl_namespace
| pl_from | 265 | NULL | 1078997 | Using
index |
| 1 | SIMPLE | recentchanges | ALL |
rc_timestamp,rc_namespace_title,rc_ns_usertext | NULL | NULL
| NULL | 176 | Using where |
| 1 | SIMPLE | page | ALL | PRIMARY,name_title
| NULL | NULL | NULL | 43475 | Using
where |
| 1 | SIMPLE | redirect | index | PRIMARY,rd_ns_title
| rd_ns_title | 265 | NULL | 8185 | Using
where; Using index |
+----+-------------+---------------+-------+------------------------------------------------+-------------+---------+------+---------+--------------------------+
4 rows in set (0.00 sec)
Note the join types, and the row numbers. The old code was a simple
ref plus range scan:
mysql> EXPLAIN SELECT * FROM pagelinks, recentchanges WHERE
rc_timestamp > '20071011094400' AND (pl_namespace=rc_namespace AND
pl_title=rc_title) AND pl_from=1234;
+----+-------------+---------------+-------+------------------------------------------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra
|
+----+-------------+---------------+-------+------------------------------------------------+--------------+---------+-------+------+-------------+
| 1 | SIMPLE | pagelinks | ref | pl_from,pl_namespace
| pl_from | 4 | const | 1 | Using
index |
| 1 | SIMPLE | recentchanges | range |
rc_timestamp,rc_namespace_title,rc_ns_usertext | rc_timestamp | 16
| NULL | 1 | Using where |
+----+-------------+---------------+-------+------------------------------------------------+--------------+---------+-------+------+-------------+
2 rows in set (0.00 sec)
Generally speaking, OR does not make MySQL happy. UNION is a good deal better:
mysql> EXPLAIN SELECT rc_namespace, rc_title FROM pagelinks,
recentchanges WHERE rc_timestamp > '20071011094400' AND
(pl_namespace=rc_namespace AND pl_title=rc_title) AND pl_from=1234
UNION SELECT rc_namespace, rc_title FROM pagelinks, recentchanges,
redirect, page WHERE rc_timestamp > '20071011094400' AND
rd_namespace=rc_namespace AND rd_title=rc_title AND rd_from=page_id
AND page_namespace=pl_namespace AND page_title=pl_title AND
pl_from=1234;
+----+--------------+---------------+--------+------------------------------------------------+--------------+---------+-----------------------------------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref
| rows | Extra |
+----+--------------+---------------+--------+------------------------------------------------+--------------+---------+-----------------------------------------------------------------+------+-------------+
| 1 | PRIMARY | pagelinks | ref | pl_from,pl_namespace
| pl_from | 4 | const
| 1 | Using index |
| 1 | PRIMARY | recentchanges | range |
rc_timestamp,rc_namespace_title,rc_ns_usertext | rc_timestamp | 16
| NULL |
1 | Using where |
| 2 | UNION | pagelinks | ref | pl_from,pl_namespace
| pl_from | 4 | const
| 1 | Using index |
| 2 | UNION | recentchanges | range |
rc_timestamp,rc_namespace_title,rc_ns_usertext | rc_timestamp | 16
| NULL |
1 | Using where |
| 2 | UNION | redirect | ref | PRIMARY,rd_ns_title
| rd_ns_title | 261 |
wikidb.recentchanges.rc_namespace,wikidb.recentchanges.rc_title | 1
| Using index |
| 2 | UNION | page | eq_ref | PRIMARY,name_title
| PRIMARY | 4 |
wikidb.redirect.rd_from | 1
| Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL
| NULL | NULL | NULL
| NULL | |
+----+--------------+---------------+--------+------------------------------------------------+--------------+---------+-----------------------------------------------------------------+------+-------------+
7 rows in set (0.00 sec)
Although not really good, given the row counts. And given the
randomness of my parameters and the oddity of my database (e.g. it's
months out of date, I picked a page that probably has practically no
incoming links, my MySQL version is different from Wikimedia's), the
UNIONized query may still be a serious problem and needs to be
examined more closely before being deployed.