[MDEV-6516] Suboptimal execution plan (index vs index_merge) with GROUP BY, AND/OR conditions - JIRA

Details

Type: Bug
Status: Open
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.0.12
Fix Version/s: 10.0
Component/s: None
Labels:
None

Description

Notes:
Encountered while running tests for ~~MDEV-6454~~, but unrelated.
EITS is used for reliability, it is not necessary to trigger the problem.

The data dump is attached.

Table structure:

CREATE TABLE `table20000_innodb_int_autoinc` (
  `col_varchar_64` varchar(64) DEFAULT NULL,
  `col_varchar_64_key` varchar(64) DEFAULT NULL,
  `col_bigint_key` bigint(20) DEFAULT NULL,
  `col_varchar_10` varchar(10) DEFAULT NULL,
  `col_smallint_key` smallint(6) DEFAULT NULL,
  `col_smallint` smallint(6) DEFAULT NULL,
  `col_varchar_10_key` varchar(10) DEFAULT NULL,
  `col_bigint` bigint(20) DEFAULT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`pk`),
  KEY `col_varchar_64_key` (`col_varchar_64_key`),
  KEY `col_bigint_key` (`col_bigint_key`),
  KEY `col_smallint_key` (`col_smallint_key`),
  KEY `col_varchar_10_key` (`col_varchar_10_key`),
  KEY `key1` (`col_smallint`,`col_bigint_key`)
) ENGINE=InnoDB AUTO_INCREMENT=20001 DEFAULT CHARSET=latin1;

Query:

SELECT col_smallint_key, MAX(col_bigint_key) FROM table20000_innodb_int_autoinc 
WHERE ( pk = 6 OR col_smallint_key = 255 ) AND col_varchar_64_key <> 'y' OR col_smallint_key IS NULL GROUP BY 1;

Execution time and plans:

"Good" plan and time:

SELECT col_smallint_key, MAX(col_bigint_key) FROM table20000_innodb_int_autoinc 
WHERE ( pk = 6 OR col_smallint_key = 255 ) AND col_varchar_64_key <> 'y' OR col_smallint_key IS NULL GROUP BY 1;
+------------------+---------------------+
| col_smallint_key | MAX(col_bigint_key) |
+------------------+---------------------+
|             NULL | 9222527611924643840 |
|                0 |               28088 |
|              255 | 6826049660210118656 |
+------------------+---------------------+
3 rows in set (0.16 sec)

+------+-------------+-------------------------------+-------------+---------------------------------------------+-------------------------------------------+---------+------+------+----------+------------------------------------------------------------------------------------------------------+
| id   | select_type | table                         | type        | possible_keys                               | key                                       | key_len | ref  | rows | filtered | Extra                                                                                                |
+------+-------------+-------------------------------+-------------+---------------------------------------------+-------------------------------------------+---------+------+------+----------+------------------------------------------------------------------------------------------------------+
|    1 | SIMPLE      | table20000_innodb_int_autoinc | index_merge | PRIMARY,col_varchar_64_key,col_smallint_key | PRIMARY,col_smallint_key,col_smallint_key | 4,3,3   | NULL | 3403 |   100.00 | Using union(PRIMARY,col_smallint_key,col_smallint_key); Using where; Using temporary; Using filesort |
+------+-------------+-------------------------------+-------------+---------------------------------------------+-------------------------------------------+---------+------+------+----------+------------------------------------------------------------------------------------------------------+

| Note  | 1003 | select `test`.`table20000_innodb_int_autoinc`.`col_smallint_key` AS `col_smallint_key`,max(`test`.`table20000_innodb_int_autoinc`.`col_bigint_key`) AS `MAX(col_bigint_key)` from `test`.`table20000_innodb_int_autoinc` where ((((`test`.`table20000_innodb_int_autoinc`.`pk` = 6) or (`test`.`table20000_innodb_int_autoinc`.`col_smallint_key` = 255)) and (`test`.`table20000_innodb_int_autoinc`.`col_varchar_64_key` <> 'y')) or isnull(`test`.`table20000_innodb_int_autoinc`.`col_smallint_key`)) group by 1 |

Bad plan and time:

SET use_stat_tables = PREFERABLY;
ANALYZE TABLE table20000_innodb_int_autoinc;
+------------------------------------+---------+----------+-----------------------------------------+
| Table                              | Op      | Msg_type | Msg_text                                |
+------------------------------------+---------+----------+-----------------------------------------+
| test.table20000_innodb_int_autoinc | analyze | status   | Engine-independent statistics collected |
| test.table20000_innodb_int_autoinc | analyze | status   | OK                                      |
+------------------------------------+---------+----------+-----------------------------------------+

SELECT col_smallint_key, MAX(col_bigint_key) FROM table20000_innodb_int_autoinc  WHERE ( pk = 6 OR col_smallint_key = 255 ) AND col_varchar_64_key <> 'y' OR col_smallint_key IS NULL GROUP BY 1;
+------------------+---------------------+
| col_smallint_key | MAX(col_bigint_key) |
+------------------+---------------------+
|             NULL | 9222527611924643840 |
|                0 |               28088 |
|              255 | 6826049660210118656 |
+------------------+---------------------+
3 rows in set (0.65 sec)

EXPLAIN EXTENDED SELECT col_smallint_key, MAX(col_bigint_key) FROM table20000_innodb_int_autoinc  WHERE ( pk = 6 OR col_smallint_key = 255 ) AND col_varchar_64_key <> 'y' OR col_smallint_key IS NULL GROUP BY 1;
+------+-------------+-------------------------------+-------+---------------------------------------------+------------------+---------+------+-------+----------+-------------+
| id   | select_type | table                         | type  | possible_keys                               | key              | key_len | ref  | rows  | filtered | Extra       |
+------+-------------+-------------------------------+-------+---------------------------------------------+------------------+---------+------+-------+----------+-------------+
|    1 | SIMPLE      | table20000_innodb_int_autoinc | index | PRIMARY,col_varchar_64_key,col_smallint_key | col_smallint_key | 3       | NULL | 20000 |   100.00 | Using where |
+------+-------------+-------------------------------+-------+---------------------------------------------+------------------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

| Note  | 1003 | select `test`.`table20000_innodb_int_autoinc`.`col_smallint_key` AS `col_smallint_key`,max(`test`.`table20000_innodb_int_autoinc`.`col_bigint_key`) AS `MAX(col_bigint_key)` from `test`.`table20000_innodb_int_autoinc` where ((((`test`.`table20000_innodb_int_autoinc`.`pk` = 6) or (`test`.`table20000_innodb_int_autoinc`.`col_smallint_key` = 255)) and (`test`.`table20000_innodb_int_autoinc`.`col_varchar_64_key` <> 'y')) or isnull(`test`.`table20000_innodb_int_autoinc`.`col_smallint_key`)) group by 1 |