Description:
For a range query with two columns, when the second column is part of the index but not in the left-most prefix, it can still be utilized, but the optimizer often does not pick up that path.
How to repeat:
mysql > show create table v1\G
*************************** 1. row ***************************
Table: v1
Create Table: CREATE TABLE `v1` (
`id` bigint NOT NULL,
`id_col1` int NOT NULL,
`col2` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
`col3` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
`col4` datetime NOT NULL,
`col5` tinyint(1) NOT NULL DEFAULT '0',
`col6` tinyint(1) NOT NULL DEFAULT '1',
`col7` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
`col8` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
`col9` tinyint(1) NOT NULL DEFAULT '0',
`col10` datetime DEFAULT NULL,
`col11` datetime DEFAULT NULL,
`col12` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`id_col1`),
UNIQUE KEY `uk` (`id_col1`,`col2`,`col7`) USING BTREE,
KEY `id_col1` (`id_col1`),
KEY `composite1` (`id_col1`,`col7`,`col5`,`col6`,`col9`,`col4`),
KEY `col10` (`col10`),
KEY `col11` (`col11`),
KEY `composite2` (`id_col1`,`col12`),
KEY `composite3` (`id_col1`,`col7`,`col8`,`col5`,`col9`,`col4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin
1 row in set (0.00 sec)
mysql > explain select id, col2, col4 from v1 where id_col1 in (1178451,1001264,1045769,580,1038583,196) and col4 > now() - interval 48 HOUR\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: v1
partitions: NULL
type: ALL
possible_keys: uk,id_col1,composite1,composite2,composite3
key: NULL
key_len: NULL
ref: NULL
rows: 131135
filtered: 11.40
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql > flush status; pager md5sum;
Query OK, 0 rows affected (0.00 sec)
PAGER set to 'md5sum'
mysql > select id, col2, col4 from v1 where id_col1 in (1178451,1001264,1045769,580,1038583,196) and col4 > now() - interval 48 HOUR\G
b38e8ba0384c1f14479970ad634f3323 -
50 rows in set (0.07 sec)
mysql > nopager; show status like 'ha%';
PAGER set to stdout
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 130001 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+--------+
18 rows in set (0.00 sec)
mysql > explain select id, col2, col4 from v1 FORCE KEY(composite1) where id_col1 in (1178451,1001264,1045769,580,1038583,196) and col4 > now() - interval 48 HOUR\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: v1
partitions: NULL
type: range
possible_keys: composite1
key: composite1
key_len: 4
ref: NULL
rows: 50720
filtered: 33.33
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
PAGER set to 'md5sum'
mysql > select id, col2, col4 from v1 FORCE KEY(composite1) where id_col1 in (1178451,1001264,1045769,580,1038583,196) and col4 > now() - interval 48 HOUR\G
b333b997136c838b2088dd5dc3c78d40 -
50 rows in set (0.01 sec)
mysql > nopager; show status like 'ha%';
PAGER set to stdout
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 6 |
| Handler_read_last | 0 |
| Handler_read_next | 50 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
mysql > explain select id, col2, col4 from v1 FORCE KEY(composite2) where id_col1 in (1178451,1001264,1045769,580,1038583,196) and col4 > now() - interval 48 HOUR\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: v1
partitions: NULL
type: range
possible_keys: composite2
key: composite2
key_len: 4
ref: NULL
rows: 52952
filtered: 33.33
Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)
PAGER set to 'md5sum'
mysql > select id, col2, col4 from v1 FORCE KEY(composite2) where id_col1 in (1178451,1001264,1045769,580,1038583,196) and col4 > now() - interval 48 HOUR\G
dd0405eb0f6056fb493a8a166f934668 -
50 rows in set (0.05 sec)
mysql > nopager; show status like 'ha%';
PAGER set to stdout
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 6 |
| Handler_read_last | 0 |
| Handler_read_next | 24937 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
When another key is added with both columns, it is used as expected, but interestingly, the handlers are the same as in the case of forcing composite1 even though the key_len now confirms both columns being used:
alter table v1 add key composite4 (id_col1,col4);
mysql > explain select id, col2, col4 from v1 where id_col1 in (1178451,1001264,1045769,580,1038583,196) and col4 > now() - interval 48 HOUR\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: v1
partitions: NULL
type: range
possible_keys: uk,id_col1,composite1,composite2,composite3,composite4
key: composite4
key_len: 9
ref: NULL
rows: 54
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
mysql [localhost:8041] {msandbox} (db1) > flush status; pager md5sum;
Query OK, 0 rows affected (0.00 sec)
PAGER set to 'md5sum'
mysql > select id, col2, col4 from v1 where id_col1 in (1178451,1001264,1045769,580,1038583,196) and col4 > now() - interval 48 HOUR\G
384fad2ab322ee553ff5c1040538d349 -
50 rows in set (0.00 sec)
mysql [localhost:8041] {msandbox} (db1) > nopager; show status like 'ha%';
PAGER set to stdout
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 6 |
| Handler_read_last | 0 |
| Handler_read_next | 50 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
Suggested fix:
Avoid choosing a suboptimal query plan for such a situation.