- mysql version：5.7.27
- environment：macos10.14-x86_64
Problem description：inconsistency between mysql explain key and optimizer trace analysis。
sql:
EXPLAIN
SELECT
id,
org_id,
org_name,
subject_code,
subject_name,
subject_value,
comment,
date_id,
ctime,
utime,
valid
FROM
xxxxxxxxx
WHERE
date_id = 20201101
AND valid = 1
AND subject_code IN (2001, 2002)
ORDER BY
id ASC
LIMIT
600, 200;
mysql explain key：
according to mysql explain, PRIMARY is used.
mysql optimizer trace：
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"IN_uses_bisection": true
},
{
"expanded_query": "/* select#1 */ select `bm_franchisee_rights_org_upload`.`id` AS `id`,`bm_franchisee_rights_org_upload`.`org_id` AS `org_id`,`bm_franchisee_rights_org_upload`.`org_name` AS `org_name`,`bm_franchisee_rights_org_upload`.`subject_code` AS `subject_code`,`bm_franchisee_rights_org_upload`.`subject_name` AS `subject_name`,`bm_franchisee_rights_org_upload`.`subject_value` AS `subject_value`,`bm_franchisee_rights_org_upload`.`comment` AS `comment`,`bm_franchisee_rights_org_upload`.`date_id` AS `date_id`,`bm_franchisee_rights_org_upload`.`ctime` AS `ctime`,`bm_franchisee_rights_org_upload`.`utime` AS `utime`,`bm_franchisee_rights_org_upload`.`valid` AS `valid` from `bm_franchisee_rights_org_upload` where ((`bm_franchisee_rights_org_upload`.`date_id` = 20201101) and (`bm_franchisee_rights_org_upload`.`valid` = 1) and (`bm_franchisee_rights_org_upload`.`subject_code` in (2001,2002))) order by `bm_franchisee_rights_org_upload`.`id` limit 0,200"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`bm_franchisee_rights_org_upload`.`date_id` = 20201101) and (`bm_franchisee_rights_org_upload`.`valid` = 1) and (`bm_franchisee_rights_org_upload`.`subject_code` in (2001,2002)))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`bm_franchisee_rights_org_upload`.`subject_code` in (2001,2002)) and multiple equal(20201101, `bm_franchisee_rights_org_upload`.`date_id`) and multiple equal(1, `bm_franchisee_rights_org_upload`.`valid`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`bm_franchisee_rights_org_upload`.`subject_code` in (2001,2002)) and multiple equal(20201101, `bm_franchisee_rights_org_upload`.`date_id`) and multiple equal(1, `bm_franchisee_rights_org_upload`.`valid`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`bm_franchisee_rights_org_upload`.`subject_code` in (2001,2002)) and multiple equal(20201101, `bm_franchisee_rights_org_upload`.`date_id`) and multiple equal(1, `bm_franchisee_rights_org_upload`.`valid`))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`bm_franchisee_rights_org_upload`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`bm_franchisee_rights_org_upload`",
"field": "date_id",
"equals": "20201101",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`bm_franchisee_rights_org_upload`",
"range_analysis": {
"table_scan": {
"rows": 2557236,
"cost": 529675
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_date_id_subject_code",
"usable": true,
"key_parts": [
"date_id",
"subject_code",
"id"
]
},
{
"index": "idx_subject_code_date_id",
"usable": true,
"key_parts": [
"subject_code",
"date_id",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_date_id_subject_code",
"ranges": [
"20201101 <= date_id <= 20201101 AND 2001 <= subject_code <= 2001",
"20201101 <= date_id <= 20201101 AND 2002 <= subject_code <= 2002"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": true,
"index_only": false,
"rows": 39576,
"cost": 41562,
"chosen": true
},
{
"index": "idx_subject_code_date_id",
"ranges": [
"2001 <= subject_code <= 2001 AND 20201101 <= date_id <= 20201101",
"2002 <= subject_code <= 2002 AND 20201101 <= date_id <= 20201101"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": true,
"index_only": false,
"rows": 39640,
"cost": 41619,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_date_id_subject_code",
"rows": 39576,
"ranges": [
"20201101 <= date_id <= 20201101 AND 2001 <= subject_code <= 2001",
"20201101 <= date_id <= 20201101 AND 2002 <= subject_code <= 2002"
]
},
"rows_for_plan": 39576,
"cost_for_plan": 41562,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`bm_franchisee_rights_org_upload`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_date_id_subject_code",
"rows": 116238,
"cost": 77926,
"chosen": true
},
{
"rows_to_scan": 39576,
"access_type": "range",
"range_details": {
"used_index": "idx_date_id_subject_code"
},
"resulting_rows": 3957.6,
"cost": 49477,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 3957.6,
"cost_for_plan": 49477,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`bm_franchisee_rights_org_upload`.`valid` = 1) and (`bm_franchisee_rights_org_upload`.`date_id` = 20201101) and (`bm_franchisee_rights_org_upload`.`subject_code` in (2001,2002)))",
"attached_conditions_computation": [
{
"table": "`bm_franchisee_rights_org_upload`",
"rechecking_index_usage": {
"recheck_reason": "low_limit",
"limit": 200,
"row_estimate": 3957.6
}
}
],
"attached_conditions_summary": [
{
"table": "`bm_franchisee_rights_org_upload`",
"attached": "((`bm_franchisee_rights_org_upload`.`valid` = 1) and (`bm_franchisee_rights_org_upload`.`date_id` = 20201101) and (`bm_franchisee_rights_org_upload`.`subject_code` in (2001,2002)))"
}
]
}
},
{
"clause_processing": {
