Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.12
-
debian buster
Description
Disclaimer: I cannot provide full query details due to PII.
While testing 10.4.12 vs 10.1 I have noticed the following query there is a big difference on runtime on the following query.
It takes around 12 seconds on 10.4.12 and 0.02 on 10.1.43
SELECT /* SpecialRecentChanges::doMainQuery */ 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, 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`, actor_rc_user.actor_user AS `rc_user`, actor_rc_user.actor_name AS `rc_user_text`, rc_actor, wl_user, wl_notificationtimestamp, 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` `actor_rc_user` ON ((actor_rc_user.actor_id = rc_actor)) LEFT JOIN `user` ON ((actor_rc_user.actor_user = user_id)) JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `watchlist` ON (wl_user = REDACTED AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) 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 = 37 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 = 38 AND (ores_goodfaith_cls.oresc_rev=rc_this_oldid) AND ores_goodfaith_cls.oresc_class = 1) WHERE ((actor_rc_user.actor_user IS NULL) OR (NOT ( (user_editcount >= 10) AND (user_registration <= '20200220144929') ))) AND rc_bot = 0 AND ((rc_this_oldid = page_latest) OR rc_type = 3) AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace IN ('6', '7')) AND (rc_timestamp >= '20200224144839') AND rc_new IN (0, 1) ORDER BY rc_timestamp DESC LIMIT 500
|
Explain on 10.4.12:
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+
|
| 1 | PRIMARY | recentchanges | ref | rc_timestamp,new_name_timestamp,tmp_2,tmp_3,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_this_oldid | tmp_2 | 1 | const | 4274159 | Using where |
|
| 1 | PRIMARY | watchlist | eq_ref | wl_user,namespace_title,wl_user_notificationtimestamp | wl_user | 265 | const,enwiki.recentchanges.rc_namespace,enwiki.recentchanges.rc_title | 1 | |
|
| 1 | PRIMARY | actor_rc_user | eq_ref | PRIMARY,actor_user | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | |
|
| 1 | PRIMARY | user | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.actor_rc_user.actor_user | 1 | 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 | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
|
| 2 | DEPENDENT SUBQUERY | change_tag | ref | change_tag_rc_tag_id,change_tag_tag_id_id | change_tag_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 | |
|
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+---------+-------------+
|
11 rows in set (0.00 sec)
|
It is picking tmp_2 as an index, while 10.1 picks tmp_3, which is a lot better:
Explain on 10.1
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+------+----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+------+----------------------------------------------------+
|
| 1 | PRIMARY | recentchanges | range | rc_timestamp,new_name_timestamp,tmp_2,tmp_3,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_this_oldid | tmp_3 | 20 | NULL | 958 | Using index condition; Using where; Using filesort |
|
| 1 | PRIMARY | watchlist | eq_ref | wl_user,namespace_title,wl_user_notificationtimestamp | wl_user | 265 | const,enwiki.recentchanges.rc_namespace,enwiki.recentchanges.rc_title | 1 | |
|
| 1 | PRIMARY | actor_rc_user | eq_ref | PRIMARY,actor_user | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | |
|
| 1 | PRIMARY | user | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.actor_rc_user.actor_user | 1 | 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 | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
|
| 2 | DEPENDENT SUBQUERY | change_tag | ref | change_tag_rc_tag_id,change_tag_tag_id_id | change_tag_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 | |
|
+------+--------------------+--------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+------+----------------------------------------------------+
|
Despite of the filesort there, it runs a lot faster and scans a lot less rows.
The optimize trace actually shows that tmp_3 index is a better election, but it chooses tmp_2 anyways:
{
|
"index": "tmp_2",
|
"ranges": ["(0,020200224144839) <= (rc_bot,rc_timestamp)"],
|
"rowid_ordered": false,
|
"using_mrr": false,
|
"index_only": false,
|
"rows": 405574,
|
"cost": 508110,
|
"chosen": false,
|
"cause": "cost"
|
},
|
{
|
"index": "tmp_3",
|
"ranges": [
|
"(6,620200224144839) <= (rc_namespace,rc_timestamp)",
|
"(7,720200224144839) <= (rc_namespace,rc_timestamp)"
|
],
|
"rowid_ordered": false,
|
"using_mrr": false,
|
"index_only": false,
|
"rows": 957,
|
"cost": 1201,
|
"chosen": true
|
|
|
"considered_execution_plans": [
|
{
|
"plan_prefix": [],
|
"table": "recentchanges",
|
"best_access_path": {
|
"considered_access_paths": [
|
{
|
"access_type": "ref",
|
"index": "tmp_2",
|
"rows": 4.27e6,
|
"cost": 440644,
|
"chosen": true
|
},
|
Forcing index tmp_3 on 10.4 shows the same result than on 10.1 and a lot faster query 12 seconds vs 0.02 query runtime even though it does filesorting
|
|
|
|
+------+--------------------+--------------------+--------+-------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+------+----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+--------------------+--------+-------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+------+----------------------------------------------------+
|
| 1 | PRIMARY | recentchanges | range | tmp_3 | tmp_3 | 20 | NULL | 962 | Using index condition; Using where; Using filesort |
|
| 1 | PRIMARY | watchlist | eq_ref | wl_user,namespace_title,wl_user_notificationtimestamp | wl_user | 265 | const,enwiki.recentchanges.rc_namespace,enwiki.recentchanges.rc_title | 1 | |
|
| 1 | PRIMARY | actor_rc_user | eq_ref | PRIMARY,actor_user | PRIMARY | 8 | enwiki.recentchanges.rc_actor | 1 | |
|
| 1 | PRIMARY | user | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.actor_rc_user.actor_user | 1 | 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 | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | enwiki.recentchanges.rc_comment_id | 1 | |
|
| 2 | DEPENDENT SUBQUERY | change_tag | ref | change_tag_rc_tag_id,change_tag_tag_id_id | change_tag_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 | |
|
+------+--------------------+--------------------+--------+-------------------------------------------------------+-----------------------+---------+-----------------------------------------------------------------------+------+----------------------------------------------------+
|
11 rows in set (0.01 sec)
|
This is the ANALYZE JSON format output on a normal run
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 12905,
|
"table": {
|
"table_name": "recentchanges",
|
"access_type": "ref",
|
"possible_keys": [
|
"rc_timestamp",
|
"new_name_timestamp",
|
"tmp_2",
|
"tmp_3",
|
"rc_name_type_patrolled_timestamp",
|
"rc_ns_actor",
|
"rc_actor",
|
"rc_namespace_title_timestamp",
|
"rc_this_oldid"
|
],
|
"key": "tmp_2",
|
"key_length": "1",
|
"used_key_parts": ["rc_bot"],
|
"ref": ["const"],
|
"r_loops": 1,
|
"rows": 4274159,
|
"r_rows": 7.28e6,
|
"r_total_time_ms": 11593,
|
"filtered": 0.0113,
|
"r_filtered": 0.0091,
|
"attached_condition": "recentchanges.rc_b
|
Hinting the optimizer to USE INDEX (tmp_3)
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 13.483,
|
"read_sorted_file": {
|
"r_rows": 663,
|
"filesort": {
|
"sort_key": "recentchanges.rc_timestamp desc",
|
"r_loops": 1,
|
"r_total_time_ms": 5.9206,
|
"r_used_priority_queue": false,
|
"r_output_rows": 663,
|
"r_buffer_size": "2047Kb",
|
"table": {
|
"table_name": "recentchanges",
|
"access_type": "range",
|
"possible_keys": ["tmp_3"],
|
"key": "tmp_3",
|
"key_length": "20",
|
"used_key_parts": ["rc_namespace", "rc_timestamp"],
|
"r_loops": 1,
|
"rows": 967,
|
"r_rows": 967,
|
"r_total_time_ms": 6.7351,
|
"filtered": 83.716,
|
"r_filtered": 68.563,
|
"index_condition": "recentchanges.rc_namespace in ('6','7') and recentchanges.rc_timestamp >= '20200224144839'",
|
"attached_condition": "recentchanges.rc_bot = 0 and recentchanges.rc_type <> 6 and recentchanges.rc_source <> 'wb' and recentchanges.rc_new in (0,1)"
|
}
|
Tables definition:
*************************** 1. row ***************************
|
Table: recentchanges
|
Create Table: CREATE TABLE `recentchanges` (
|
`rc_id` int(8) NOT NULL AUTO_INCREMENT,
|
`rc_timestamp` varbinary(14) NOT NULL DEFAULT '',
|
`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 NOT NULL,
|
PRIMARY KEY (`rc_id`),
|
KEY `rc_timestamp` (`rc_timestamp`),
|
KEY `rc_cur_id` (`rc_cur_id`),
|
KEY `new_name_timestamp` (`rc_new`,`rc_namespace`,`rc_timestamp`),
|
KEY `rc_ip` (`rc_ip`),
|
KEY `tmp_2` (`rc_bot`,`rc_timestamp`),
|
KEY `tmp_3` (`rc_namespace`,`rc_timestamp`),
|
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`)
|
) ENGINE=InnoDB AUTO_INCREMENT=1234530860 DEFAULT CHARSET=binary
|
*************************** 1. row ***************************
|
Table: watchlist
|
Create Table: CREATE TABLE `watchlist` (
|
`wl_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`wl_user` int(5) unsigned NOT NULL DEFAULT 0,
|
`wl_namespace` int(11) NOT NULL DEFAULT 0,
|
`wl_title` varbinary(255) NOT NULL DEFAULT '',
|
`wl_notificationtimestamp` varbinary(14) DEFAULT NULL,
|
PRIMARY KEY (`wl_id`),
|
UNIQUE KEY `wl_user` (`wl_user`,`wl_namespace`,`wl_title`),
|
KEY `namespace_title` (`wl_namespace`,`wl_title`),
|
KEY `wl_user_notificationtimestamp` (`wl_user`,`wl_notificationtimestamp`)
|
) ENGINE=InnoDB AUTO_INCREMENT=244510744 DEFAULT CHARSET=binary
|
*************************** 1. row ***************************
|
Table: actor
|
Create Table: CREATE TABLE `actor` (
|
`actor_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`actor_user` int(10) unsigned DEFAULT NULL,
|
`actor_name` varbinary(255) NOT NULL,
|
PRIMARY KEY (`actor_id`),
|
UNIQUE KEY `actor_name` (`actor_name`),
|
UNIQUE KEY `actor_user` (`actor_user`)
|
) ENGINE=InnoDB AUTO_INCREMENT=201849245 DEFAULT CHARSET=binary
|
*************************** 1. row ***************************
|
Table: user
|
Create Table: CREATE TABLE `user` (
|
`user_id` int(5) unsigned NOT NULL AUTO_INCREMENT,
|
`user_name` varbinary(255) NOT NULL DEFAULT '',
|
`user_real_name` varbinary(255) NOT NULL DEFAULT '',
|
`user_password` tinyblob NOT NULL,
|
`user_newpassword` tinyblob NOT NULL,
|
`user_email` tinyblob NOT NULL,
|
`user_touched` varbinary(14) NOT NULL DEFAULT '',
|
`user_token` varbinary(32) NOT NULL DEFAULT '',
|
`user_email_authenticated` varbinary(14) DEFAULT NULL,
|
`user_email_token` varbinary(32) DEFAULT NULL,
|
`user_email_token_expires` varbinary(14) DEFAULT NULL,
|
`user_registration` varbinary(14) DEFAULT NULL,
|
`user_newpass_time` varbinary(14) DEFAULT NULL,
|
`user_editcount` int(11) DEFAULT NULL,
|
`user_password_expires` varbinary(14) DEFAULT NULL,
|
PRIMARY KEY (`user_id`),
|
UNIQUE KEY `user_name` (`user_name`),
|
KEY `user_email_token` (`user_email_token`),
|
KEY `user_email` (`user_email`(50))
|
) ENGINE=InnoDB AUTO_INCREMENT=38630081 DEFAULT CHARSET=binary
|
*************************** 1. row ***************************
|
Table: page
|
Create Table: CREATE TABLE `page` (
|
`page_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
|
`page_namespace` int(11) NOT NULL DEFAULT 0,
|
`page_title` varbinary(255) NOT NULL DEFAULT '',
|
`page_restrictions` tinyblob NOT NULL,
|
`page_is_redirect` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`page_is_new` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`page_random` double unsigned NOT NULL DEFAULT 0,
|
`page_touched` varbinary(14) NOT NULL DEFAULT '',
|
`page_links_updated` varbinary(14) DEFAULT NULL,
|
`page_latest` int(8) unsigned NOT NULL DEFAULT 0,
|
`page_len` int(8) unsigned NOT NULL DEFAULT 0,
|
`page_content_model` varbinary(32) DEFAULT NULL,
|
`page_lang` varbinary(35) DEFAULT NULL,
|
PRIMARY KEY (`page_id`),
|
UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
|
KEY `page_random` (`page_random`),
|
KEY `page_len` (`page_len`),
|
KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`)
|
) ENGINE=InnoDB AUTO_INCREMENT=63204770 DEFAULT CHARSET=binary
|
*************************** 1. row ***************************
|
Table: flaggedpages
|
Create Table: CREATE TABLE `flaggedpages` (
|
`fp_page_id` int(10) unsigned NOT NULL,
|
`fp_reviewed` tinyint(1) NOT NULL DEFAULT 0,
|
`fp_pending_since` binary(14) DEFAULT NULL,
|
`fp_stable` int(10) unsigned NOT NULL,
|
`fp_quality` tinyint(1) DEFAULT NULL,
|
PRIMARY KEY (`fp_page_id`),
|
KEY `fp_reviewed_page` (`fp_reviewed`,`fp_page_id`),
|
KEY `fp_quality_page` (`fp_quality`,`fp_page_id`),
|
KEY `fp_pending_since` (`fp_pending_since`)
|
) ENGINE=InnoDB DEFAULT CHARSET=binary
|
*************************** 1. row ***************************
|
Table: ores_classification
|
Create Table: CREATE TABLE `ores_classification` (
|
`oresc_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`oresc_rev` int(10) unsigned NOT NULL,
|
`oresc_model` smallint(6) NOT NULL,
|
`oresc_class` tinyint(4) NOT NULL,
|
`oresc_probability` decimal(3,3) NOT NULL,
|
`oresc_is_predicted` tinyint(1) NOT NULL,
|
PRIMARY KEY (`oresc_id`),
|
UNIQUE KEY `oresc_rev_model_class` (`oresc_rev`,`oresc_model`,`oresc_class`)
|
) ENGINE=InnoDB AUTO_INCREMENT=505922062 DEFAULT CHARSET=binary
|
*************************** 1. row ***************************
|
Table: comment
|
Create Table: CREATE TABLE `comment` (
|
`comment_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`comment_hash` int(11) NOT NULL,
|
`comment_text` blob NOT NULL,
|
`comment_data` blob DEFAULT NULL,
|
PRIMARY KEY (`comment_id`),
|
KEY `comment_hash` (`comment_hash`)
|
) ENGINE=InnoDB AUTO_INCREMENT=311512696 DEFAULT CHARSET=binary
|
*************************** 1. row ***************************
|
Table: change_tag
|
Create Table: CREATE TABLE `change_tag` (
|
`ct_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`ct_rc_id` int(11) DEFAULT NULL,
|
`ct_log_id` int(10) unsigned DEFAULT NULL,
|
`ct_rev_id` int(10) unsigned DEFAULT NULL,
|
`ct_params` blob DEFAULT NULL,
|
`ct_tag_id` int(10) unsigned NOT NULL,
|
PRIMARY KEY (`ct_id`),
|
UNIQUE KEY `change_tag_rc_tag_id` (`ct_rc_id`,`ct_tag_id`),
|
UNIQUE KEY `change_tag_log_tag_id` (`ct_log_id`,`ct_tag_id`),
|
UNIQUE KEY `change_tag_rev_tag_id` (`ct_rev_id`,`ct_tag_id`),
|
KEY `change_tag_tag_id_id` (`ct_tag_id`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=106682245 DEFAULT CHARSET=binary
|
Attachments
Issue Links
- relates to
-
-
- Stalled
-
Activity
Thanks for taking a look. If you believe this is a case of MDEV-23707, can we include this one on that list?.
That meta task is set for 10.6, do you think that's still the case or any chances it can be done on 10.5? (I assume 10.4 is not realistic).
For now we won't touch
optimizer_use_condition_selectivity
|
as this is not a super frequent query and it might have negative impact on the other queries arriving to that host.
Thank you!
Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.
Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.
Back to the main issue.
marostegui, I think the cause of this behavior is this one: https://jira.mariadb.org/browse/MDEV-23707
and the only way out currently is to use a hint or set optimizer_use_condition_selectivity to 1 (the default value before the 10.4. In 10.4 it was changed to 4). This may have a negative effect though, as this will disable use of condition selectivity.
We do intend to fix MDEV-23707 but I can't give a date ATM.