Here's the optimizer trace that indicates that MySQL thinks that the cost for the index scan is higher than the cost for a full table scan. This is of course wrong.
{ "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `BIG_STORAGE_TOKU`.`EXPIRE_DATE` AS `EXPIRE_DATE` from `BIG_STORAGE_TOKU` where (`BIG_STORAGE_TOKU`.`HASH_ID` = sha2('70164',256))" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`BIG_STORAGE_TOKU`.`HASH_ID` = sha2('70164',256))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal(sha2('70164',256), `BIG_STORAGE_TOKU`.`HASH_ID`)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal(sha2('70164',256), `BIG_STORAGE_TOKU`.`HASH_ID`)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal(sha2('70164',256), `BIG_STORAGE_TOKU`.`HASH_ID`)" } ] } }, { "table_dependencies": [ { "table": "`BIG_STORAGE_TOKU`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`BIG_STORAGE_TOKU`", "field": "HASH_ID", "equals": "sha2('70164',256)", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`BIG_STORAGE_TOKU`", "range_analysis": { "table_scan": { "rows": 100030, "cost": 53351 }, "potential_range_indices": [ { "index": "HASH_ID_IX", "usable": true, "key_parts": [ "HASH_ID" ] }, { "index": "EXPIRE_DATE_IX", "usable": false, "cause": "not_applicable" } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "HASH_ID_IX", "ranges": [ "ffff925a44dfc908915841bdafe81d3ee3b5238610e4901d0d806e4740881084 <= HASH_ID <= ffff925a44dfc908915841bdafe81d3ee3b5238610e4901d0d806e4740881084" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 239680, "cost": 287617, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`BIG_STORAGE_TOKU`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "HASH_ID_IX", "rows": 239680, "cost": 57939, "chosen": true }, { "access_type": "scan", "rows": 75023, "cost": 53349, "chosen": true } ] }, "cost_for_plan": 53349, "rows_for_plan": 75023, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "(`BIG_STORAGE_TOKU`.`HASH_ID` = sha2('70164',256))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`BIG_STORAGE_TOKU`", "attached": "(`BIG_STORAGE_TOKU`.`HASH_ID` = sha2('70164',256))" } ] } }, { "refine_plan": [ { "table": "`BIG_STORAGE_TOKU`", "access_type": "table_scan" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] }
Here's the optimizer trace that indicates that MySQL thinks that the cost for the index scan is higher than the cost for a full table scan. This is of course wrong.
{ join_preparatio n": {
"expanded_ query": "/* select#1 */ select `BIG_STORAGE_ TOKU`.` EXPIRE_ DATE` AS `EXPIRE_DATE` from `BIG_STORAGE_TOKU` where (`BIG_STORAGE_ TOKU`.` HASH_ID` = sha2('70164',256))" join_optimizati on": {
"condition _processing" : {
"condition" : "WHERE",
"original_ condition" : "(`BIG_ STORAGE_ TOKU`.` HASH_ID` = sha2('70164' ,256))" ,
"steps" : [
"transforma tion": "equality_ propagation" ,
"resulting_ condition" : "multiple equal(sha2( '70164' ,256), `BIG_STORAGE_ TOKU`.` HASH_ID` )"
"transforma tion": "constant_ propagation" ,
"resulting_ condition" : "multiple equal(sha2( '70164' ,256), `BIG_STORAGE_ TOKU`.` HASH_ID` )"
"transforma tion": "trivial_ condition_ removal" ,
"resulting_ condition" : "multiple equal(sha2( '70164' ,256), `BIG_STORAGE_ TOKU`.` HASH_ID` )"
"table_ dependencies" : [
"table" : "`BIG_STORAGE_ TOKU`",
"row_ may_be_ null": false,
"map_ bit": 0,
"depends_ on_map_ bits": [
"ref_ optimizer_ key_uses" : [
"table" : "`BIG_STORAGE_ TOKU`",
"field" : "HASH_ID",
"equals" : "sha2(' 70164', 256)",
"null_ rejecting" : false
"rows_ estimation" : [
"table" : "`BIG_STORAGE_ TOKU`",
"range_ analysis" : {
"table_ scan": {
" rows": 100030,
" cost": 53351
},
"potential_ range_indices" : [
{
"index" : "HASH_ID_IX",
"usable" : true,
"key_parts" : [
"HASH_ ID"
]
} ,
{
"index" : "EXPIRE_DATE_IX",
"usable" : false,
"cause" : "not_applicable"
}
],
"setup_ range_condition s": [
],
"group_ index_range" : {
" chosen" : false,
" cause": "not_group_ by_or_distinct"
},
"analyzing_ range_alternati ves": {
" range_scan_ alternatives" : [
{
"index" : "HASH_ID_IX",
"ranges" : [
"ffff925a4 4dfc908915841bd afe81d3ee3b5238 610e4901d0d806e 4740881084 <= HASH_ID <= ffff925a44dfc90 8915841bdafe81d 3ee3b5238610e49 01d0d806e474088 1084"
],
"index_ dives_for_ eq_ranges" : true,
"rowid_ ordered" : true,
"using_ mrr": false,
"index_ only": false,
"rows" : 239680,
"cost" : 287617,
"chosen" : false,
"cause" : "cost"
}
] ,
" analyzing_ roworder_ intersect" : {
"usable" : false,
"cause" : "too_few_ roworder_ scans"
}
"considere d_execution_ plans": [
"plan_ prefix" : [
"table" : "`BIG_STORAGE_ TOKU`",
"best_ access_ path": {
"considered _access_ paths": [
{
"access_ type": "ref",
"index" : "HASH_ID_IX",
"rows" : 239680,
"cost" : 57939,
"chosen" : true
} ,
{
"access_ type": "scan",
"rows" : 75023,
"cost" : 53349,
"chosen" : true
}
"cost_ for_plan" : 53349,
"rows_ for_plan" : 75023,
"chosen" : true
"attaching _conditions_ to_tables" : {
"original_ condition" : "(`BIG_ STORAGE_ TOKU`.` HASH_ID` = sha2('70164' ,256))" ,
"attached_ conditions_ computation" : [
"attached_ conditions_ summary" : [
"table" : "`BIG_STORAGE_ TOKU`",
"attached" : "(`BIG_ STORAGE_ TOKU`.` HASH_ID` = sha2('70164',256))"
"refine_ plan": [
"table" : "`BIG_STORAGE_ TOKU`",
"access_ type": "table_scan" join_execution" : {
"steps": [
{
"
"select#": 1,
"steps": [
{
}
]
}
},
{
"
"select#": 1,
"steps": [
{
{
},
{
},
{
}
]
}
},
{
{
]
}
]
},
{
{
}
]
},
{
{
}
}
}
]
},
{
{
],
]
},
}
]
},
{
],
{
}
]
}
},
{
{
}
]
}
]
}
},
{
"
"select#": 1,
"steps": [
]
}
}
]
}