|
Notes:
Encountered while running tests for MDEV-6454, but unrelated.
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 |
|
|
index_vs_index_merge.dump.gz