|
We have noticed the optimizer preferring a full scan rather than the proper index on a query when upgrading from 10.4.22 to 10.4.24
root@db1123.eqiad.wmnet[mediawikiwiki]> select @@version ; nopager; 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`,rc_title,rc_namespace,wl_user,wl_notificationtimestamp,we_expiry,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` 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 `watchlist` ON (wl_user = 14096723 AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) LEFT JOIN `watchlist_expiry` ON ((wl_id = we_item)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) WHERE rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199)) AND (rc_timestamp >= '20220316090254') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 100;
|
+---------------------+
|
| @@version |
|
+---------------------+
|
| 10.4.22-MariaDB-log |
|
+---------------------+
|
1 row in set (0.001 sec)
|
|
PAGER set to stdout
|
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+-------------------------------------------------------------------------------------+-------+-------------+
|
| 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_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_new_name_timestamp | rc_timestamp | 14 | NULL | 33098 | Using where |
|
| 1 | PRIMARY | watchlist | eq_ref | wl_user,wl_user_notificationtimestamp,wl_namespace_title | wl_user | 265 | const,mediawikiwiki.recentchanges.rc_namespace,mediawikiwiki.recentchanges.rc_title | 1 | |
|
| 1 | PRIMARY | watchlist_expiry | eq_ref | PRIMARY | PRIMARY | 4 | mediawikiwiki.watchlist.wl_id | 1 | Using where |
|
| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | mediawikiwiki.recentchanges.rc_cur_id | 1 | |
|
| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | mediawikiwiki.recentchanges.rc_comment_id | 1 | |
|
| 1 | PRIMARY | recentchanges_actor | eq_ref | PRIMARY | PRIMARY | 8 | mediawikiwiki.recentchanges.rc_actor | 1 | |
|
| 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 5 | mediawikiwiki.recentchanges.rc_id | 1 | Using index |
|
| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | mediawikiwiki.change_tag.ct_tag_id | 1 | |
|
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+-------------------------------------------------------------------------------------+-------+-------------+
|
8 rows in set (0.002 sec)
|
The query on 10.4.22 takes 0.03 and on 10.4.24 the optimizer picks no index and the query takes more than 60 seconds.
root@db1112.eqiad.wmnet[mediawikiwiki]> select @@version ; nopager; 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`,rc_title,rc_namespace,wl_user,wl_notificationtimestamp,we_expiry,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` 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 `watchlist` ON (wl_user = 14096723 AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) LEFT JOIN `watchlist_expiry` ON ((wl_id = we_item)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) WHERE rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199)) AND (rc_timestamp >= '20220316090254') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 100;
|
+---------------------+
|
| @@version |
|
+---------------------+
|
| 10.4.24-MariaDB-log |
|
+---------------------+
|
1 row in set (0.000 sec)
|
|
PAGER set to stdout
|
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+-------------------------------------------------------------------------------------+----------+------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+-------------------------------------------------------------------------------------+----------+------------------------------------+
|
| 1 | PRIMARY | recentchanges_actor | ALL | PRIMARY | NULL | NULL | NULL | 15842120 | Using temporary; Using filesort |
|
| 1 | PRIMARY | recentchanges | ref | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_new_name_timestamp | rc_actor | 8 | mediawikiwiki.recentchanges_actor.actor_id | 4 | Using index condition; Using where |
|
| 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | mediawikiwiki.recentchanges.rc_comment_id | 1 | |
|
| 1 | PRIMARY | watchlist | eq_ref | wl_user,wl_user_notificationtimestamp,wl_namespace_title | wl_user | 265 | const,mediawikiwiki.recentchanges.rc_namespace,mediawikiwiki.recentchanges.rc_title | 1 | |
|
| 1 | PRIMARY | watchlist_expiry | eq_ref | PRIMARY | PRIMARY | 4 | mediawikiwiki.watchlist.wl_id | 1 | Using where |
|
| 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | mediawikiwiki.recentchanges.rc_cur_id | 1 | |
|
| 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 5 | mediawikiwiki.recentchanges.rc_id | 1 | Using index |
|
| 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | mediawikiwiki.change_tag.ct_tag_id | 1 | |
|
+------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+-------------------------------------------------------------------------------------+----------+------------------------------------+
|
8 rows in set (0.003 sec)
|
We've optimized, analyzed and fully rebuilt the table but that made no difference for the optimizer (or the stats). It keeps choosing to do a full scan.
This is the analyze:
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 24957,
|
"filesort": {
|
"sort_key": "recentchanges.rc_timestamp desc",
|
"r_loops": 1,
|
"r_total_time_ms": 5.0222,
|
"r_limit": 100,
|
"r_used_priority_queue": true,
|
"r_output_rows": 101,
|
"temporary_table": {
|
"table": {
|
"table_name": "recentchanges_actor",
|
"access_type": "ALL",
|
"possible_keys": ["PRIMARY"],
|
"r_loops": 1,
|
"rows": 15842121,
|
"r_rows": 1.78e7,
|
"r_total_time_ms": 3924.8,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"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_new_name_timestamp"
|
],
|
"key": "rc_actor",
|
"key_length": "8",
|
"used_key_parts": ["rc_actor"],
|
"ref": ["mediawikiwiki.recentchanges_actor.actor_id"],
|
"r_loops": 17788620,
|
"rows": 9,
|
"r_rows": 9.8e-4,
|
"r_total_time_ms": 19329,
|
"filtered": 100,
|
"r_filtered": 47.244,
|
"index_condition": "recentchanges.rc_timestamp >= '20220316090254'",
|
"attached_condition": "recentchanges.rc_bot = 0 and recentchanges.rc_type <> 6 and recentchanges.rc_source <> 'wb' and recentchanges.rc_namespace not in (1198,1199) and recentchanges.rc_new in (0,1)"
|
},
|
"table": {
|
"table_name": "comment_rc_comment",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "8",
|
"used_key_parts": ["comment_id"],
|
"ref": ["mediawikiwiki.recentchanges.rc_comment_id"],
|
"r_loops": 8270,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 12.884,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "watchlist",
|
"access_type": "eq_ref",
|
"possible_keys": [
|
"wl_user",
|
"wl_user_notificationtimestamp",
|
"wl_namespace_title"
|
],
|
"key": "wl_user",
|
"key_length": "265",
|
"used_key_parts": ["wl_user", "wl_namespace", "wl_title"],
|
"ref": [
|
"const",
|
"mediawikiwiki.recentchanges.rc_namespace",
|
"mediawikiwiki.recentchanges.rc_title"
|
],
|
"r_loops": 8270,
|
"rows": 1,
|
"r_rows": 0.017,
|
"r_total_time_ms": 16.65,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "watchlist_expiry",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["we_item"],
|
"ref": ["mediawikiwiki.watchlist.wl_id"],
|
"r_loops": 8270,
|
"rows": 1,
|
"r_rows": 0,
|
"r_total_time_ms": 0.1871,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "trigcond(trigcond(watchlist.wl_id is not null))"
|
},
|
"table": {
|
"table_name": "page",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["page_id"],
|
"ref": ["mediawikiwiki.recentchanges.rc_cur_id"],
|
"r_loops": 8270,
|
"rows": 1,
|
"r_rows": 0.6671,
|
"r_total_time_ms": 10.732,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"subqueries": [
|
{
|
"expression_cache": {
|
"state": "disabled",
|
"r_loops": 0,
|
"query_block": {
|
"select_id": 2,
|
"r_loops": 8270,
|
"r_total_time_ms": 27.913,
|
"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": ["mediawikiwiki.recentchanges.rc_id"],
|
"r_loops": 8270,
|
"rows": 1,
|
"r_rows": 0.3435,
|
"r_total_time_ms": 15.135,
|
"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": ["mediawikiwiki.change_tag.ct_tag_id"],
|
"r_loops": 2841,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 2.492,
|
"filtered": 100,
|
"r_filtered": 100
|
}
|
}
|
}
|
}
|
]
|
}
|
}
|
}
|
} |
|
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=6896299 DEFAULT CHARSET=binary
|
|