https://bugzilla.wikimedia.org/show_bug.cgi?id=45619
Web browser: ---
Bug ID: 45619
Summary: IndexPager::buildQueryInfo (contributions page
unfiltered) query needs tuning
Product: MediaWiki
Version: 1.21-git
Hardware: All
OS: All
Status: NEW
Severity: normal
Priority: Unprioritized
Component: Database
Assignee: [email protected]
Reporter: [email protected]
CC: [email protected]
Classification: Unclassified
Mobile Platform: ---
The FORCE INDEX provided in this query results in a /much/ slower query plan
than without. This is the case with both MySQL 5.1-facebook, and MariaDB 5.5,
so it should just be removed.
Query:
SELECT /* IndexPager::buildQueryInfo (contributions page unfiltered) user */
rev_id,rev_page,rev_text_id,rev_timestamp,rev_comment,rev_user_text,rev_user,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,user_name,page_namespace,page_title,page_is_new,page_latest,page_is_redirect,page_len,ts_tags
FROM `revision` FORCE INDEX (user_timestamp) INNER JOIN `page` ON ((page_id =
rev_page)) LEFT JOIN `user` ON ((rev_user != 0) AND (user_id = rev_user)) LEFT
JOIN `user_groups` ON ((ug_user = rev_user) AND ug_group = 'bot') LEFT JOIN
`tag_summary` ON ((ts_rev_id=rev_id)) WHERE (rev_user >18370655) AND (ug_group
IS NULL) AND ((rev_deleted & 4) = 0) AND (rev_timestamp<'20130301192103')
ORDER BY rev_timestamp DESC LIMIT 51;
Times of the query as above:
production enwiki master - mysql 5.1-facebook: 51 rows in set (7 min 23.03
sec)
mariadb 5.5.29: 51 rows in set (1.29 sec)
Times with the FORCE INDEX (user_timestamp) removed
production enwiki master - mysql 5.1-facebook: 51 rows in set (0.26 sec)
mariadb 5.5.29: 51 rows in set (0.20 sec)
The EXPLAIN without the FORCE INDEX looks worse on 5.1 without than with, based
on number of rows examined, but it avoids a filesort. It looks better without
the FORCE INDEX on mariadb than mysql 5.1 with or without. In both cases,
rev_timestamp is used instead of user_timestamp.
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
You are watching all bug changes.
_______________________________________________
Wikibugs-l mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
