Page MenuHomePhabricator

IndexPager::buildQueryInfo (NewPagesPager) query needs tuning
Closed, ResolvedPublic

Description

The following query is much slower on MariaDB enwiki with the FORCE INDEX than
without it:

SELECT /* IndexPager::buildQueryInfo (NewPagesPager) xxx.xxx.xxx.xxx */ rc_namespace, rc_title, rc_cur_id, rc_user, rc_user_text, rc_comment, rc_timestamp, rc_patrolled, rc_id, rc_deleted, page_len AS length, page_latest AS rev_id, rc_this_oldid, page_namespace, page_title, fp_stable, fp_pending_since, (SELECT GROUP_CONCAT(ct_tag SEPARATOR ', ') FROM change_tag WHERE ct_rc_id=rc_id ) AS ts_tags FROM recentchanges FORCE INDEX (new_name_timestamp) INNER JOIN page ON ((page_id=rc_cur_id)) LEFT JOIN flaggedpages ON ((fp_page_id = rc_cur_id)) WHERE rc_new = '1' AND (rc_namespace != '6') AND page_is_redirect = '0' ORDER BY rc_timestamp DESC LIMIT 51;

With force: > 1min, ~500000 rows hit with a filesort.

Without force: < 1ses, ~100 rows hit using rc_timestamp, no filesort.


Version: 1.23.0
Severity: major

Details

Reference
bz58256

Event Timeline

bzimport raised the priority of this task from to High.
bzimport set Reference to bz58256.
bzimport added a subscriber: Unknown Object (MLST).
Springle created this task.Dec 10 2013, 6:14 AM

Change 100537 had a related patch set uploaded by Springle:
Remove problem forced index. Bug 58256.

https://gerrit.wikimedia.org/r/100537

Change 100537 had a related patch set uploaded by Aaron Schulz:
Remove problem forced index. Bug 58256.

https://gerrit.wikimedia.org/r/100537

Change 100537 merged by jenkins-bot:
Remove problem forced index

https://gerrit.wikimedia.org/r/100537

bd808 moved this task from Tag to Archive on the Performance board.Mar 11 2015, 7:31 PM