r45585 MediaWiki - Code Review archive

Repository:	MediaWiki
Revision:	< r45584‎ \| r45585 \| r45586 >
Date:	22:02, 8 January 2009
Author:	btongminh
Status:	ok (Comments)
Tags:	slow query
Comment:	Force the use of the PRIMARY key rather than the name_title in the second query.
Modified paths:	/trunk/phase3/includes/api/ApiQueryBacklinks.php (modified) (history)

Diff [purge]

Index: trunk/phase3/includes/api/ApiQueryBacklinks.php
—	—	@@ -169,6 +169,7 @@
170	170	$this->addWhereFld('page_is_redirect', 0);
171	171	$this->addOption('LIMIT', $this->params['limit'] + 1);
172	172	$this->addOption('ORDER BY', $this->bl_sort);
	173	+ $this->addOption('USE INDEX', array('page' => 'PRIMARY'));
173	174	}
174	175
175	176	private function run($resultPageSet = null) {

Comments

#Comment by Brion VIBBER (talk | contribs) 22:06, 8 January 2009

Note this is a fix for the case where a namespace filter is used; MySQL 4 picks the namespace/title index on page, and ends up doing a giant filesort instead of just following the join on page_id and checking the where clause on the result. We found a hojillion of queries like this stuck:

Original:

mysql> explain SELECT /* ApiQueryBacklinks::run::secondQuery 212.143.232.2 */ page_id,page_title,page_namespace,page_is_redirect,pl_title,pl_namespace FROM `page`,`pagelinks` WHERE (pl_from=page_id) AND ((pl_title = 'The_Blue_Raja' AND pl_namespace = '0') OR (pl_title = 'Blessed_Disciples_of_Hippocrates' AND pl_namespace = '0') OR (pl_title = 'Psychofrakulator' AND pl_namespace = '0') OR (pl_title = 'The_Shoveller' AND pl_namespace = '0') OR (pl_title = 'The_Shoveler' AND pl_namespace = '0') OR (pl_title = 'Blue_Raja' AND pl_namespace = '0') OR (pl_title = 'Champion_City' AND pl_namespace = '0') OR (pl_title = 'Mr._Furious' AND pl_namespace = '0') OR (pl_title = 'The_Bowler' AND pl_namespace = '0') OR (pl_title = 'Kinka_Usher' AND pl_namespace = '0') OR (pl_title = 'Mystery_Men_(film)' AND pl_namespace = '0') OR (pl_title = 'Psychofraculator' AND pl_namespace = '0') OR (pl_title = 'Funky_skunkulator' AND pl_namespace = '0') OR (pl_title = 'Psycho-frakulator' AND pl_namespace = '0') OR (pl_title = 'Psycho-fraculator' AND pl_namespace = '0')) AND page_namespace = '0' AND page_is_redirect = '1' ORDER BY pl_namespace, pl_title, pl_from LIMIT 101;

+-----------+------+----------------------+------------+---------+--------------------+---------+----------------------------------------------+
| table     | type | possible_keys        | key        | key_len | ref                | rows    | Extra                                        |
+-----------+------+----------------------+------------+---------+--------------------+---------+----------------------------------------------+
| page      | ref  | PRIMARY,name_title   | name_title |       4 | const              | 8349216 | Using where; Using temporary; Using filesort |
| pagelinks | ref  | pl_from,pl_namespace | pl_from    |       8 | page.page_id,const |       1 | Using where; Using index                     |
+-----------+------+----------------------+------------+---------+--------------------+---------+----------------------------------------------+

Original without the 'page_namespace' in the WHERE clause:

mysql> explain SELECT /* ApiQueryBacklinks::run::secondQuery 212.143.232.2 */ page_id,page_title,page_namespace,page_is_redirect,pl_title,pl_namespace FROM `page`,`pagelinks` WHERE (pl_from=page_id) AND ((pl_title = 'The_Blue_Raja' AND pl_namespace = '0') OR (pl_title = 'Blessed_Disciples_of_Hippocrates' AND pl_namespace = '0') OR (pl_title = 'Psychofrakulator' AND pl_namespace = '0') OR (pl_title = 'The_Shoveller' AND pl_namespace = '0') OR (pl_title = 'The_Shoveler' AND pl_namespace = '0') OR (pl_title = 'Blue_Raja' AND pl_namespace = '0') OR (pl_title = 'Champion_City' AND pl_namespace = '0') OR (pl_title = 'Mr._Furious' AND pl_namespace = '0') OR (pl_title = 'The_Bowler' AND pl_namespace = '0') OR (pl_title = 'Kinka_Usher' AND pl_namespace = '0') OR (pl_title = 'Mystery_Men_(film)' AND pl_namespace = '0') OR (pl_title = 'Psychofraculator' AND pl_namespace = '0') OR (pl_title = 'Funky_skunkulator' AND pl_namespace = '0') OR (pl_title = 'Psycho-frakulator' AND pl_namespace = '0') OR (pl_title = 'Psycho-fraculator' AND pl_namespace = '0')) AND page_is_redirect = '1' ORDER BY pl_namespace, pl_title, pl_from LIMIT 101;

+-----------+--------+----------------------+--------------+---------+-------------------+------+--------------------------+
| table     | type   | possible_keys        | key          | key_len | ref               | rows | Extra                    |
+-----------+--------+----------------------+--------------+---------+-------------------+------+--------------------------+
| pagelinks | range  | pl_from,pl_namespace | pl_namespace |     259 | NULL              |   25 | Using where; Using index |
| page      | eq_ref | PRIMARY              | PRIMARY      |       4 | pagelinks.pl_from |    1 | Using where              |
+-----------+--------+----------------------+--------------+---------+-------------------+------+--------------------------+
2 rows in set (0.00 sec)

Original with forced primary index on page:

mysql> explain SELECT /* ApiQueryBacklinks::run::secondQuery 212.143.232.2 */ page_id,page_title,page_namespace,page_is_redirect,pl_title,pl_namespace FROM `page` FORCE INDEX (PRIMARY),`pagelinks` WHERE (pl_from=page_id) AND ((pl_title = 'The_Blue_Raja' AND pl_namespace = '0') OR (pl_title = 'Blessed_Disciples_of_Hippocrates' AND pl_namespace = '0') OR (pl_title = 'Psychofrakulator' AND pl_namespace = '0') OR (pl_title = 'The_Shoveller' AND pl_namespace = '0') OR (pl_title = 'The_Shoveler' AND pl_namespace = '0') OR (pl_title = 'Blue_Raja' AND pl_namespace = '0') OR (pl_title = 'Champion_City' AND pl_namespace = '0') OR (pl_title = 'Mr._Furious' AND pl_namespace = '0') OR (pl_title = 'The_Bowler' AND pl_namespace = '0') OR (pl_title = 'Kinka_Usher' AND pl_namespace = '0') OR (pl_title = 'Mystery_Men_(film)' AND pl_namespace = '0') OR (pl_title = 'Psychofraculator' AND pl_namespace = '0') OR (pl_title = 'Funky_skunkulator' AND pl_namespace = '0') OR (pl_title = 'Psycho-frakulator' AND pl_namespace = '0') OR (pl_title = 'Psycho-fraculator' AND pl_namespace = '0')) AND page_namespace = '0' AND page_is_redirect = '1' ORDER BY pl_namespace, pl_title, pl_from LIMIT 101;

+-----------+--------+----------------------+--------------+---------+-------------------+------+--------------------------+
| table     | type   | possible_keys        | key          | key_len | ref               | rows | Extra                    |
+-----------+--------+----------------------+--------------+---------+-------------------+------+--------------------------+
| pagelinks | range  | pl_from,pl_namespace | pl_namespace |     259 | NULL              |   25 | Using where; Using index |
| page      | eq_ref | PRIMARY              | PRIMARY      |       4 | pagelinks.pl_from |    1 | Using where              |
+-----------+--------+----------------------+--------------+---------+-------------------+------+--------------------------+
2 rows in set (0.00 sec)

(same w/ "use index)

mysql> explain SELECT /* ApiQueryBacklinks::run::secondQuery 212.143.232.2 */ page_id,page_title,page_namespace,page_is_redirect,pl_title,pl_namespace FROM `page` USE INDEX (PRIMARY),`pagelinks` WHERE (pl_from=page_id) AND ((pl_title = 'The_Blue_Raja' AND pl_namespace = '0') OR (pl_title = 'Blessed_Disciples_of_Hippocrates' AND pl_namespace = '0') OR (pl_title = 'Psychofrakulator' AND pl_namespace = '0') OR (pl_title = 'The_Shoveller' AND pl_namespace = '0') OR (pl_title = 'The_Shoveler' AND pl_namespace = '0') OR (pl_title = 'Blue_Raja' AND pl_namespace = '0') OR (pl_title = 'Champion_City' AND pl_namespace = '0') OR (pl_title = 'Mr._Furious' AND pl_namespace = '0') OR (pl_title = 'The_Bowler' AND pl_namespace = '0') OR (pl_title = 'Kinka_Usher' AND pl_namespace = '0') OR (pl_title = 'Mystery_Men_(film)' AND pl_namespace = '0') OR (pl_title = 'Psychofraculator' AND pl_namespace = '0') OR (pl_title = 'Funky_skunkulator' AND pl_namespace = '0') OR (pl_title = 'Psycho-frakulator' AND pl_namespace = '0') OR (pl_title = 'Psycho-fraculator' AND pl_namespace = '0')) AND page_namespace = '0' AND page_is_redirect = '1' ORDER BY pl_namespace, pl_title, pl_from LIMIT 101;

+-----------+--------+----------------------+--------------+---------+-------------------+------+--------------------------+
| table     | type   | possible_keys        | key          | key_len | ref               | rows | Extra                    |
+-----------+--------+----------------------+--------------+---------+-------------------+------+--------------------------+
| pagelinks | range  | pl_from,pl_namespace | pl_namespace |     259 | NULL              |   25 | Using where; Using index |
| page      | eq_ref | PRIMARY              | PRIMARY      |       4 | pagelinks.pl_from |    1 | Using where              |
+-----------+--------+----------------------+--------------+---------+-------------------+------+--------------------------+
2 rows in set (0.00 sec)

Status & tagging log

23:57, 13 January 2009 Brion VIBBER (talk | contribs) changed the tags for r45585 [added: slow query]
23:57, 13 January 2009 Brion VIBBER (talk | contribs) changed the status of r45585 [removed: new added: ok]