Example query on enwiki:
SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,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,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,recentchanges_comment.comment_text AS `rc_comment_text`,recentchanges_comment.comment_data AS `rc_comment_data`,recentchanges_comment.comment_id AS `rc_comment_id`,we_expiry,page_latest,wl_notificationtimestamp,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE (ct_rc_id=rc_id) ) AS `ts_tags`,fp_stable,fp_pending_since,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score` FROM `recentchanges` STRAIGHT_JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) STRAIGHT_JOIN `comment` `recentchanges_comment` ON ((comment_id=rc_comment_id)) JOIN `watchlist` ON (wl_user = 48747569 AND (wl_namespace=rc_namespace) AND (wl_title=rc_title)) LEFT JOIN `watchlist_expiry` ON ((wl_id = we_item)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 59 AND (ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class = 1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON (ores_goodfaith_cls.oresc_model = 60 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1) WHERE (rc_type != 6) AND (rc_source != 'wb') AND (rc_timestamp >= '20250730013715') AND ((we_expiry IS NULL OR we_expiry > '20250829013715')) AND ((rc_type != 3 OR (rc_deleted & 1) != 1)) ORDER BY rc_timestamp DESC LIMIT 1000 # Query_time: 2.845925 Lock_time: 0.000491 Rows_sent: 1000 Rows_examined: 695804 select count(*) from watchlist where wl_user=48747569; 2288 select count(*) from recentchanges join watchlist on (wl_user = 48747569 AND (wl_namespace=rc_namespace) AND (wl_title=rc_title)); 61515
Explain:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | watchlist | ref | wl_user,wl_user_notificationtimestamp,wl_namespace_title | wl_user_notificationtimestamp | 4 | const | 2333 | Using temporary; Using filesort |
| 1 | PRIMARY | watchlist_expiry | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.watchlist.wl_id | 1 | Using where |
| 1 | PRIMARY | recentchanges | ref | rc_timestamp, rc_name_type_patrolled_timestamp, rc_ns_actor, rc_actor, rc_namespace_title_timestamp, rc_source_name_timestamp, rc_name_source_patrolled_timestamp | rc_namespace_title_timestamp | 261 | enwiki.watchlist.wl_namespace, enwiki.watchlist.wl_title | 1 | Using index condition; Using where |
| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.recentchanges.rc_cur_id | 1 | |
| 1 | PRIMARY | flaggedpages | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.recentchanges.rc_cur_id | 1 | |
| 1 | PRIMARY | ores_damaging_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
| 1 | PRIMARY | ores_goodfaith_cls | eq_ref | oresc_rev_model_class | oresc_rev_model_class | 7 | enwiki.recentchanges.rc_this_oldid,const,const | 1 | |
| 1 | PRIMARY | recentchanges_actor | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | |
| 1 | PRIMARY | recentchanges_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
| 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 9 | enwiki.recentchanges.rc_id | 1 | Using index |
| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.change_tag.ct_tag_id | 1 | |
The user is asking for the first 1k of 61k rows, but 695k rows are examined. The multiplier is because of the large number of joins which collect data needed for display. A temporary table is constructed with 61k rows, with all joins being resolved, before the temporary table is truncated to 1k rows. This issue probably affects other ChangesListSpecialPage subclasses.
Doing the filters on a subquery in the FROM clause and then doing the joins for display in the outer query reduces the number of rows examined to ~140k:
SELECT rc.rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,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,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,recentchanges_comment.comment_text AS `rc_comment_text`,recentchanges_comment.comment_data AS `rc_comment_data`,recentchanges_comment.comment_id AS `rc_comment_id`,we_expiry,page_latest,wl_notificationtimestamp,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE (ct_rc_id=rc_sq.rc_id) ) AS `ts_tags`,fp_stable,fp_pending_since,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score` FROM (SELECT rc_id FROM `recentchanges` JOIN `watchlist` ON (wl_user = 48747569 AND (wl_namespace=rc_namespace) AND (wl_title=rc_title)) LEFT JOIN `watchlist_expiry` ON ((wl_id = we_item)) WHERE (rc_type != 6) AND (rc_source != 'wb') AND (rc_timestamp >= '20250730013715') AND ((we_expiry IS NULL OR we_expiry > '20250829013715')) AND ((rc_type != 3 OR (rc_deleted & 1) != 1)) ORDER BY rc_timestamp DESC LIMIT 1000) AS rc_sq JOIN recentchanges AS rc ON rc.rc_id=rc_sq.rc_id JOIN `watchlist` ON (wl_user = 48747569 AND (wl_namespace=rc_namespace) AND (wl_title=rc_title)) LEFT JOIN `watchlist_expiry` ON ((wl_id = we_item)) STRAIGHT_JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) STRAIGHT_JOIN `comment` `recentchanges_comment` ON ((comment_id=rc_comment_id)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 59 AND (ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class = 1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON (ores_goodfaith_cls.oresc_model = 60 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1) # Query_time: 0.429881 Lock_time: 0.000557 Rows_sent: 1000 Rows_examined: 140392
If the timestamp range is known or can be guessed, the resulting query is several times faster still, with only 16k rows examined:
SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,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,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,recentchanges_comment.comment_text AS `rc_comment_text`,recentchanges_comment.comment_data AS `rc_comment_data`,recentchanges_comment.comment_id AS `rc_comment_id`,we_expiry,page_latest,wl_notificationtimestamp,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE (ct_rc_id=rc_id) ) AS `ts_tags`,fp_stable,fp_pending_since,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,ores_goodfaith_cls.oresc_probability AS `ores_goodfaith_score` FROM `recentchanges` STRAIGHT_JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) STRAIGHT_JOIN `comment` `recentchanges_comment` ON ((comment_id=rc_comment_id)) JOIN `watchlist` ON (wl_user = 48747569 AND (wl_namespace=rc_namespace) AND (wl_title=rc_title)) LEFT JOIN `watchlist_expiry` ON ((wl_id = we_item)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) LEFT JOIN `flaggedpages` ON ((fp_page_id = rc_cur_id)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 59 AND (ores_damaging_cls.oresc_rev=rc_this_oldid) AND ores_damaging_cls.oresc_class = 1) LEFT JOIN `ores_classification` `ores_goodfaith_cls` ON (ores_goodfaith_cls.oresc_model = 60 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1) WHERE (rc_type != 6) AND (rc_source != 'wb') AND rc_timestamp BETWEEN '20250829142829' AND '20250829235200' AND ((we_expiry IS NULL OR we_expiry > '20250829013715')) AND ((rc_type != 3 OR (rc_deleted & 1) != 1)) ORDER BY rc_timestamp DESC LIMIT 1000 # Query_time: 0.103520 Lock_time: 0.000531 Rows_sent: 1000 Rows_examined: 16077
