Description:
Hi,there is a problem when i use union index,following is my experimental environment:
System environment: Description: Ubuntu 14.04.1 LTS
Mysql version: 5.7.18-log
Explaination about key_len bu authoritative documents of MySQL 5.7:
Table 8.1 EXPLAIN Output Columns
key_len key_length The length of the chosen key
Explaination about used_key_parts by percon
[https://www.percona.com/blog/2015/12/17/used_key_parts-explain-formatjson-provides-insight...]
EXPLAIN FORMAT=JSON stores this information in the used_key_parts member.
I found when use union index,if where condition is <= or >=,the value of key_len different with used_key_parts’ value.
How to repeat:
Example:
Data sheet：
s | CREATE TABLE `s` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
Value in data sheet：
id | a | b | c | d |
+----+------+------+------+------+
| 1 | 1 | 10 | 5 | 8 |
| 2 | 2 | 9 | 6 | 1 |
| 3 | 3 | 8 | 7 | 5 |
| 4 | 4 | 7 | 8 | 2 |
| 5 | 5 | 6 | 9 | 4 |
| 6 | 6 | 5 | 10 | 0 |
| 7 | 7 | 4 | 4 | 5 |
| 8 | 8 | 3 | 3 | 7 |
| 9 | 9 | 2 | 2 | 8 |
| 10 | 10 | 1 | 1 | 6 |
The sentence that test select ：
explain format = json select * from s where a <=3 and b=5\G;
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.81"
},
"table": {
"table_name": "s",
"access_type": "range",
"possible_keys": [
"a_b_c"
],
"key": "a_b_c",
"used_key_parts": [
"a"
],
"key_length": "10",
"rows_examined_per_scan": 2,
"rows_produced_per_join": 0,
"filtered": "10.00",
"index_condition": "((`test`.`s`.`b` = 5) and (`test`.`s`.`a` <= 3))",
"cost_info": {
"read_cost": "3.77",
"eval_cost": "0.04",
"prefix_cost": "3.81",
"data_read_per_join": "4"
},
"used_columns": [
"id",
"a",
"b",
"c",
"d"
]
}
}
}
Suggested fix:
It is recommended that the value if key_len be changed to the same length as the value of used_key_parts
