Bug #110111 Optimizer chooses worst plan when index condition pushdown opportunity
Submitted: 17 Feb 2023 12:53 Modified: 20 Feb 2023 12:30
Reporter: Przemyslaw Malkowski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:8.0.31 OS:Any
Assigned to: CPU Architecture:Any
Tags: ICP, Optimizer

[17 Feb 2023 12:53] Przemyslaw Malkowski
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.
[17 Feb 2023 13:02] Przemyslaw Malkowski
Table dump uploaded via SFTP /support/incoming/mysql-bug-data-110111.zst
[20 Feb 2023 12:30] MySQL Verification Team
Hello Przemyslaw,

Thank you for the report. 

Thanks,
Umesh