Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.7
-
None
-
None
-
debian bullseye
Description
We are testing 10.6.7 in production coming from 10.4.22
We have noticed a query regression on a specific query that has gone from 0.6 seconds in 10.4 to more than 6 seconds 10.6. The query planner seems to pick the wrong index.
Table:
CREATE TABLE `recentchanges` ( |
`rc_id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
`rc_timestamp` binary(14) NOT NULL, |
`rc_actor` bigint(20) unsigned NOT NULL, |
`rc_namespace` int(11) NOT NULL DEFAULT 0, |
`rc_title` varbinary(255) NOT NULL DEFAULT '', |
`rc_comment_id` bigint(20) unsigned NOT NULL, |
`rc_minor` tinyint(3) unsigned NOT NULL DEFAULT 0, |
`rc_bot` tinyint(3) unsigned NOT NULL DEFAULT 0, |
`rc_new` tinyint(3) unsigned NOT NULL DEFAULT 0, |
`rc_cur_id` int(10) unsigned NOT NULL DEFAULT 0, |
`rc_this_oldid` int(10) unsigned NOT NULL DEFAULT 0, |
`rc_last_oldid` int(10) unsigned NOT NULL DEFAULT 0, |
`rc_type` tinyint(3) unsigned NOT NULL DEFAULT 0, |
`rc_source` varbinary(16) NOT NULL DEFAULT '', |
`rc_patrolled` tinyint(3) unsigned NOT NULL DEFAULT 0, |
`rc_ip` varbinary(40) NOT NULL DEFAULT '', |
`rc_old_len` int(10) DEFAULT NULL, |
`rc_new_len` int(10) DEFAULT NULL, |
`rc_deleted` tinyint(1) unsigned NOT NULL DEFAULT 0, |
`rc_logid` int(10) unsigned NOT NULL DEFAULT 0, |
`rc_log_type` varbinary(255) DEFAULT NULL, |
`rc_log_action` varbinary(255) DEFAULT NULL, |
`rc_params` blob DEFAULT NULL, |
PRIMARY KEY (`rc_id`), |
KEY `rc_timestamp` (`rc_timestamp`), |
KEY `rc_cur_id` (`rc_cur_id`), |
KEY `rc_ip` (`rc_ip`), |
KEY `rc_name_type_patrolled_timestamp` (`rc_namespace`,`rc_type`,`rc_patrolled`,`rc_timestamp`), |
KEY `rc_ns_actor` (`rc_namespace`,`rc_actor`), |
KEY `rc_actor` (`rc_actor`,`rc_timestamp`), |
KEY `rc_namespace_title_timestamp` (`rc_namespace`,`rc_title`,`rc_timestamp`), |
KEY `rc_this_oldid` (`rc_this_oldid`), |
KEY `rc_new_name_timestamp` (`rc_new`,`rc_namespace`,`rc_timestamp`) |
) ENGINE=InnoDB AUTO_INCREMENT=1488317684 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 |
Query:
SELECT rc_id,rc_timestamp,rc_namespace,rc_title,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,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(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` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.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) WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 500; |
Explain on 10.4
mysql:root@localhost [enwiki]> explain SELECT rc_id,rc_timestamp,rc_namespace,rc_title,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,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(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` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.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) WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 500;
|
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+------------------------------------------------+---------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+------------------------------------------------+---------+-------------+
|
| 1 | PRIMARY | recentchanges | range | rc_timestamp,rc_name_type_patrolled_timestamp,rc_namespace_title_timestamp,rc_ns_actor,rc_actor,rc_this_oldid,rc_new_name_timestamp | rc_timestamp | 14 | NULL | 4502872 | Using where |
|
| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.recentchanges.rc_cur_id | 1 | Using where |
|
| 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,actor_user | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | Using where |
|
| 1 | PRIMARY | comment_rc_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 | 5 | 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 | |
|
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+------------------------------------------------+---------+-------------+
|
9 rows in set (0.001 sec)
|
Explain on 10.6
root@db1132.eqiad.wmnet[enwiki]> explain SELECT rc_id,rc_timestamp,rc_namespace,rc_title,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,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(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` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.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) WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 500;
|
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------------------+---------+----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------------------+---------+----------------------------------------------------+
|
| 1 | PRIMARY | recentchanges | ref | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_this_oldid,rc_new_name_timestamp | rc_name_type_patrolled_timestamp | 4 | const | 4807730 | Using index condition; Using where; Using filesort |
|
| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.recentchanges.rc_cur_id | 1 | Using where |
|
| 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,actor_user | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | Using where |
|
| 1 | PRIMARY | comment_rc_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 | 5 | 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 | |
|
+------+--------------------+---------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+------------------------------------------------+---------+----------------------------------------------------+
|
9 rows in set (0.002 sec)
|
10.6 seems to prefer rc_name_type_patrolled_timestamp whereas 10.4 likes rc_timestamp which is definitely a lot faster.
10.4
mysql:root@localhost [enwiki]> pager cat > /dev/null; SELECT rc_id,rc_timestamp,rc_namespace,rc_title,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,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(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` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.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) WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 500;
|
PAGER set to 'cat > /dev/null'
|
500 rows in set (0.045 sec)
|
10.6
root@db1132.eqiad.wmnet[enwiki]> pager cat > /dev/null; SELECT rc_id,rc_timestamp,rc_namespace,rc_title,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,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(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` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.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) WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 500;
|
PAGER set to 'cat > /dev/null'
|
500 rows in set (6.770 sec)
|
Forcing the index on 10.6 is indeed a lot faster:
 |
root@db1132.eqiad.wmnet[enwiki]> pager cat > /dev/null; SELECT rc_id,rc_timestamp,rc_namespace,rc_title,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,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,page_latest,(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` USE INDEX(rc_timestamp) JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.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) WHERE (actor_user IS NOT NULL) AND rc_bot = 0 AND (rc_minor = 0) AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace = 0) AND (rc_timestamp >= '20220316104610') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 500;
|
PAGER set to 'cat > /dev/null'
|
500 rows in set (0.050 sec)
|
Refreshing the table stats made no difference, these are the analyze output for the 10.6 host:
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 6713.761803,
|
"read_sorted_file": {
|
"r_rows": 850,
|
"filesort": {
|
"sort_key": "recentchanges.rc_timestamp desc",
|
"r_loops": 1,
|
"r_total_time_ms": 6667.316059,
|
"r_used_priority_queue": false,
|
"r_output_rows": 586902,
|
"r_sort_passes": 1,
|
"r_buffer_size": "2047Kb",
|
"r_sort_mode": "sort_key,rowid",
|
"table": {
|
"table_name": "recentchanges",
|
"access_type": "ref",
|
"possible_keys": [
|
"rc_timestamp",
|
"rc_name_type_patrolled_timestamp",
|
"rc_ns_actor",
|
"rc_actor",
|
"rc_namespace_title_timestamp",
|
"rc_this_oldid",
|
"rc_new_name_timestamp"
|
],
|
"key": "rc_name_type_patrolled_timestamp",
|
"key_length": "4",
|
"used_key_parts": ["rc_namespace"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 4809336,
|
"r_rows": 1213787,
|
"r_table_time_ms": 4247.18697,
|
"r_other_time_ms": 1829.652348,
|
"filtered": 25.22198677,
|
"r_filtered": 48.35296473,
|
"index_condition": "recentchanges.rc_type <> 6 and recentchanges.rc_timestamp >= '20220316104610'",
|
"attached_condition": "recentchanges.rc_namespace <=> 0 and recentchanges.rc_bot = 0 and recentchanges.rc_minor = 0 and recentchanges.rc_source <> 'wb' and recentchanges.rc_new in (0,1)"
|
}
|
}
|
},
|
"table": {
|
"table_name": "page",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["page_id"],
|
"ref": ["enwiki.recentchanges.rc_cur_id"],
|
"r_loops": 850,
|
"rows": 1,
|
"r_rows": 1,
|
"r_table_time_ms": 4.28310043,
|
"r_other_time_ms": 0.23695731,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "trigcond(`page`.page_latest = recentchanges.rc_this_oldid or recentchanges.rc_type = 3)"
|
},
|
"table": {
|
"table_name": "flaggedpages",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["fp_page_id"],
|
"ref": ["enwiki.recentchanges.rc_cur_id"],
|
"r_loops": 633,
|
"rows": 1,
|
"r_rows": 0.004739336,
|
"r_table_time_ms": 0.930605778,
|
"r_other_time_ms": 0.099958823,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "ores_damaging_cls",
|
"access_type": "eq_ref",
|
"possible_keys": ["oresc_rev_model_class"],
|
"key": "oresc_rev_model_class",
|
"key_length": "7",
|
"used_key_parts": ["oresc_rev", "oresc_model", "oresc_class"],
|
"ref": ["enwiki.recentchanges.rc_this_oldid", "const", "const"],
|
"r_loops": 633,
|
"rows": 1,
|
"r_rows": 0.988941548,
|
"r_table_time_ms": 3.887335944,
|
"r_other_time_ms": 0.101188314,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "ores_goodfaith_cls",
|
"access_type": "eq_ref",
|
"possible_keys": ["oresc_rev_model_class"],
|
"key": "oresc_rev_model_class",
|
"key_length": "7",
|
"used_key_parts": ["oresc_rev", "oresc_model", "oresc_class"],
|
"ref": ["enwiki.recentchanges.rc_this_oldid", "const", "const"],
|
"r_loops": 633,
|
"rows": 1,
|
"r_rows": 0.988941548,
|
"r_table_time_ms": 3.651214086,
|
"r_other_time_ms": 0.122668732,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "recentchanges_actor",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "actor_user"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["actor_id"],
|
"ref": ["enwiki.recentchanges.rc_actor"],
|
"r_loops": 633,
|
"rows": 1,
|
"r_rows": 1,
|
"r_table_time_ms": 11.682509,
|
"r_other_time_ms": 0.123198762,
|
"filtered": 37.21720886,
|
"r_filtered": 78.98894155,
|
"attached_condition": "recentchanges_actor.actor_user is not null"
|
},
|
"table": {
|
"table_name": "comment_rc_comment",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["comment_id"],
|
"ref": ["enwiki.recentchanges.rc_comment_id"],
|
"r_loops": 500,
|
"rows": 1,
|
"r_rows": 1,
|
"r_table_time_ms": 10.50027619,
|
"r_other_time_ms": 0.266287853,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"subqueries": [
|
{
|
"expression_cache": {
|
"state": "disabled",
|
"r_loops": 0,
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 500,
|
"r_total_time_ms": 6.092014043,
|
"table": {
|
"table_name": "change_tag",
|
"access_type": "ref",
|
"possible_keys": ["ct_rc_tag_id", "ct_tag_id_id"],
|
"key": "ct_rc_tag_id",
|
"key_length": "5",
|
"used_key_parts": ["ct_rc_id"],
|
"ref": ["enwiki.recentchanges.rc_id"],
|
"r_loops": 500,
|
"rows": 1,
|
"r_rows": 1.166,
|
"r_table_time_ms": 2.667950323,
|
"r_other_time_ms": 0.355783187,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "change_tag_def",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["ctd_id"],
|
"ref": ["enwiki.change_tag.ct_tag_id"],
|
"r_loops": 583,
|
"rows": 1,
|
"r_rows": 1,
|
"r_table_time_ms": 2.534762923,
|
"r_other_time_ms": 0.213634327,
|
"filtered": 100,
|
"r_filtered": 100
|
}
|
}
|
}
|
}
|
]
|
}
|
} |
|
|
And this is the analyze with the index forced:
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 45.41443968,
|
"table": {
|
"table_name": "recentchanges",
|
"access_type": "range",
|
"possible_keys": ["rc_timestamp"],
|
"key": "rc_timestamp",
|
"key_length": "14",
|
"used_key_parts": ["rc_timestamp"],
|
"r_loops": 1,
|
"rows": 4787238,
|
"r_rows": 2532,
|
"r_table_time_ms": 12.71978732,
|
"r_other_time_ms": 0.939924493,
|
"filtered": 23.93359375,
|
"r_filtered": 33.96524487,
|
"attached_condition": "recentchanges.rc_bot = 0 and recentchanges.rc_minor = 0 and recentchanges.rc_namespace = 0 and recentchanges.rc_type <> 6 and recentchanges.rc_source <> 'wb' and recentchanges.rc_timestamp >= '20220316104610' and recentchanges.rc_new in (0,1)"
|
},
|
"table": {
|
"table_name": "page",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["page_id"],
|
"ref": ["enwiki.recentchanges.rc_cur_id"],
|
"r_loops": 860,
|
"rows": 1,
|
"r_rows": 1,
|
"r_table_time_ms": 4.612835802,
|
"r_other_time_ms": 0.235044521,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "trigcond(`page`.page_latest = recentchanges.rc_this_oldid or recentchanges.rc_type = 3)"
|
},
|
"table": {
|
"table_name": "flaggedpages",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["fp_page_id"],
|
"ref": ["enwiki.recentchanges.rc_cur_id"],
|
"r_loops": 625,
|
"rows": 1,
|
"r_rows": 0.0048,
|
"r_table_time_ms": 0.975657195,
|
"r_other_time_ms": 0.100794275,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "ores_damaging_cls",
|
"access_type": "eq_ref",
|
"possible_keys": ["oresc_rev_model_class"],
|
"key": "oresc_rev_model_class",
|
"key_length": "7",
|
"used_key_parts": ["oresc_rev", "oresc_model", "oresc_class"],
|
"ref": ["enwiki.recentchanges.rc_this_oldid", "const", "const"],
|
"r_loops": 625,
|
"rows": 1,
|
"r_rows": 0.9968,
|
"r_table_time_ms": 4.233134395,
|
"r_other_time_ms": 0.100245296,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "ores_goodfaith_cls",
|
"access_type": "eq_ref",
|
"possible_keys": ["oresc_rev_model_class"],
|
"key": "oresc_rev_model_class",
|
"key_length": "7",
|
"used_key_parts": ["oresc_rev", "oresc_model", "oresc_class"],
|
"ref": ["enwiki.recentchanges.rc_this_oldid", "const", "const"],
|
"r_loops": 625,
|
"rows": 1,
|
"r_rows": 0.9968,
|
"r_table_time_ms": 3.981898607,
|
"r_other_time_ms": 0.121919111,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "recentchanges_actor",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY", "actor_user"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["actor_id"],
|
"ref": ["enwiki.recentchanges.rc_actor"],
|
"r_loops": 625,
|
"rows": 1,
|
"r_rows": 1,
|
"r_table_time_ms": 4.772067115,
|
"r_other_time_ms": 0.119394921,
|
"filtered": 37.40314865,
|
"r_filtered": 80,
|
"attached_condition": "recentchanges_actor.actor_user is not null"
|
},
|
"table": {
|
"table_name": "comment_rc_comment",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["comment_id"],
|
"ref": ["enwiki.recentchanges.rc_comment_id"],
|
"r_loops": 500,
|
"rows": 1,
|
"r_rows": 1,
|
"r_table_time_ms": 8.330237845,
|
"r_other_time_ms": 0.287118971,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"subqueries": [
|
{
|
"expression_cache": {
|
"state": "disabled",
|
"r_loops": 0,
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 500,
|
"r_total_time_ms": 3.700628355,
|
"table": {
|
"table_name": "change_tag",
|
"access_type": "ref",
|
"possible_keys": ["ct_rc_tag_id", "ct_tag_id_id"],
|
"key": "ct_rc_tag_id",
|
"key_length": "5",
|
"used_key_parts": ["ct_rc_id"],
|
"ref": ["enwiki.recentchanges.rc_id"],
|
"r_loops": 500,
|
"rows": 1,
|
"r_rows": 1.168,
|
"r_table_time_ms": 2.200897109,
|
"r_other_time_ms": 0.342922022,
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
},
|
"table": {
|
"table_name": "change_tag_def",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["ctd_id"],
|
"ref": ["enwiki.change_tag.ct_tag_id"],
|
"r_loops": 584,
|
"rows": 1,
|
"r_rows": 1,
|
"r_table_time_ms": 0.624504941,
|
"r_other_time_ms": 0.216490134,
|
"filtered": 100,
|
"r_filtered": 100
|
}
|
}
|
}
|
}
|
]
|
}
|
} |
|
Optimizer traces for 10.6 without forcing the index: https://phabricator.wikimedia.org/P23007
Optimizer traces for 10.6 forcing the index: https://phabricator.wikimedia.org/P23006