Bug #87440 use union index,the value of key_len different with used_key_parts’ value.
Submitted: 16 Aug 2017 14:26
Reporter: kid zhang Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:5.7.18 OS:Ubuntu
Assigned to:
Tags: key_len, used_key_parts

[16 Aug 2017 14:26] kid zhang
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