Page MenuHomePhabricator

SpecialRecentChanges::doMainQuery query needs tuning
Closed, ResolvedPublic

Description

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

SELECT /* SpecialRecentChanges::doMainQuery */ rc_id,rc_timestamp,rc_cur_time,rc_user,rc_user_text,rc_namespace,rc_title,rc_comment,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,wl_user,wl_notificationtimestamp,ts_tags,fp_stable,fp_pending_since FROM recentchanges FORCE INDEX (rc_timestamp) LEFT JOIN watchlist ON (wl_user = '12744321' AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) LEFT JOIN tag_summary ON ((ts_rc_id=rc_id)) LEFT JOIN flaggedpages ON ((fp_page_id = rc_cur_id)) WHERE (rc_timestamp >= '20131018000000') AND rc_bot = '0' AND (rc_namespace = '828') AND (rc_type != 5) AND rc_new IN ('0','1') ORDER BY rc_timestamp DESC LIMIT 500

With force: > 1 minute, a slow range scan on rc_timestamp, ~2 million rows.

Without force: < 3 seconds, a fast ref lookup on rc_namespace_title with cheap filesort, ~1000 rows.


Version: 1.21.x
Severity: major

Details

Reference
bz57175

Event Timeline

bzimport raised the priority of this task from to High.Nov 22 2014, 2:18 AM
bzimport set Reference to bz57175.
bzimport added a subscriber: Unknown Object (MLST).
Springle created this task.Nov 18 2013, 8:52 AM

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

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

Change 100532 merged by jenkins-bot:
Remove problem forced index. Bug 57175.

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