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.
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.