Bug #95823 EXPLAIN FORMAT=JSON shows incorrect information in used_key_parts
Submitted: 15 Jun 2019 10:54 Modified: 16 Jun 2019 5:54
Reporter: Sergei Petrunia Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0, 5.7.26, 8.0.16 OS:Any
Assigned to: CPU Architecture:Any

[15 Jun 2019 10:54] Sergei Petrunia
Description:
EXPLAIN FORMAT=JSON shows incorrect information in used_key_parts for multi-part keys.

How to repeat:
## Create the dataset

create table ten(a int primary key);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table one_k(a int primary key);
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;

create table t1 (start_date date, end_date date, filler char(100), key(start_date, end_date));
insert into t1 select date_add(now(), interval a day), date_add(now(), interval (a+7) day), 'data' from one_k;

##
## First, let's use a range on the first key part
##

mysql> explain select * from t1 force index(start_date) where start_date >= '2019-02-10';
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+                               
|  1 | SIMPLE      | t1    | NULL       | range | start_date    | start_date | 4       | NULL | 1000 |   100.00 | Using index condition |                               
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+                               
1 row in set, 1 warning (0.00 sec)                                                                                                                                      
                                                                                                                                                                        
mysql> explain format=json select * from t1 force index(start_date) where start_date >= '2019-02-10'\G
*************************** 1. row ***************************                                                                                                          
EXPLAIN: {                                                                                                                                                              
  "query_block": {                                                                                                                                                      
    "select_id": 1,                                                                                                                                                     
    "cost_info": {                                                                                                                                                      
      "query_cost": "450.26"                                                                                                                                            
    },                                                                                                                                                                  
    "table": {                                                                                                                                                          
      "table_name": "t1",                                                                                                                                               
      "access_type": "range",                                                                                                                                           
      "possible_keys": [                                                                                                                                                
        "start_date"                                                                                                                                                    
      ],                                                                                                                                                                
      "key": "start_date",                                                                                                                                              
      "used_key_parts": [
        "start_date"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 1000,
      "rows_produced_per_join": 1000,
      "filtered": "100.00",
      "index_condition": "(`test4`.`t1`.`start_date` >= DATE'2019-02-10')",
      "cost_info": {
        "read_cost": "350.26",
        "eval_cost": "100.00",
        "prefix_cost": "450.26",
        "data_read_per_join": "398K"
      },
      "used_columns": [
        "start_date",
        "end_date",
        "filler"
      ]
    }
  }
}

##
## Ok, key_length=4, and used_keyparts shows "start_date". Good so far.
## Now, let's try two key parts.
##

mysql> explain select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01';
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | start_date    | start_date | 8       | NULL | 1000 |    33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

## key_len=8 now.

mysql> explain format=json select * from t1 force index(start_date) where start_date >= '2019-02-10' and end_date <'2019-04-01'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "450.26"
    },
    "table": {
      "table_name": "t1",
      "access_type": "range",
      "possible_keys": [
        "start_date"
      ],
      "key": "start_date",
      "used_key_parts": [
        "start_date"
      ],
      "key_length": "8",
      "rows_examined_per_scan": 1000,
      "rows_produced_per_join": 333,

...

## key_length=8, but used_key_parts is still  just "start_date"?  There's a clear mismatch here.
[15 Jun 2019 11:07] Sergei Petrunia
See also https://bugs.mysql.com/bug.php?id=95824
[16 Jun 2019 5:54] Umesh Shastry
Hello Sergei,

Thank you for the report and test case.
Verified as described.

regards,
Umesh