|
Performance regression , "index_merge" optimization not used
> show create table mytable
|
CREATE TABLE `mytable` (
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`show` enum('Y','N') CHARACTER SET utf8 NOT NULL,
|
`city` int(10) unsigned NOT NULL,
|
`info` tinyint(1) NOT NULL DEFAULT 0,
|
`uses` tinyint(4) NOT NULL,
|
`subd` varchar(127) CHARACTER SET utf8 DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `subd` (`subd`),
|
KEY `info` (`info`),
|
KEY `show` (`show`)
|
) ENGINE=InnoDB AUTO_INCREMENT=5454915 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
|
optimization ok
mariadb 10.3.28
> explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
|
+------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
|
| 1 | SIMPLE | mytable | index_merge | subd,info,show | info,subd | 1,384 | NULL | 59 | Using sort_union(info,subd); Using where |
|
+------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
|
mariadb 10.3.29
> explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
|
+------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
|
| 1 | SIMPLE | mytable | index_merge | subd,info,show | info,subd | 1,384 | NULL | 59 | Using sort_union(info,subd); Using where |
|
+------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
|
1 row in set (0.002 sec)
|
mariadb 10.4.18
> explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
|
+------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
|
| 1 | SIMPLE | mytable | index_merge | subd,info,show | info,subd | 1,384 | NULL | 59 | Using sort_union(info,subd); Using where |
|
+------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
|
mariadb 10.5.9
> explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
|
+------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
|
| 1 | SIMPLE | mytable | index_merge | subd,info,show | info,subd | 1,384 | NULL | 59 | Using sort_union(info,subd); Using where |
|
+------+-------------+---------+-------------+----------------+-----------+---------+------+------+------------------------------------------+
|
mysql 5.7.33
> explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
|
+----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
|
| 1 | SIMPLE | mytable | NULL | index_merge | subd,info,show | info,subd | 1,384 | NULL | 59 | 50.00 | Using sort_union(info,subd); Using where |
|
+----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
|
mysql 5.7.34
> explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
|
+----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
|
| 1 | SIMPLE | mytable | NULL | index_merge | subd,info,show | info,subd | 1,384 | NULL | 59 | 50.00 | Using sort_union(info,subd); Using where |
|
+----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
|
mysql 8.0.25
> explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
|
+----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
|
| 1 | SIMPLE | mytable | NULL | index_merge | subd,info,show | info,subd | 1,384 | NULL | 59 | 50.00 | Using sort_union(info,subd); Using where |
|
+----+-------------+---------+------------+-------------+----------------+-----------+---------+------+------+----------+------------------------------------------+
|
optimization miss
mariadb 10.4.19
> explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
|
+------+-------------+---------+------+----------------+------+---------+-------+---------+------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------+------+----------------+------+---------+-------+---------+------------------------------------+
|
| 1 | SIMPLE | mytable | ref | subd,info,show | show | 1 | const | 1771114 | Using index condition; Using where |
|
+------+-------------+---------+------+----------------+------+---------+-------+---------+------------------------------------+
|
mariadb 10.5.10
explain SELECT `id`, `city`, `uses`, `subd` FROM `mytable` WHERE `show`="Y" AND (`info`=1 OR (`subd`!="" AND `uses`=1));
|
+------+-------------+---------+------+----------------+------+---------+------+---------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------+------+----------------+------+---------+------+---------+-------------+
|
| 1 | SIMPLE | mytable | ALL | subd,info,show | NULL | NULL | NULL | 3518748 | Using where |
|
+------+-------------+---------+------+----------------+------+---------+------+---------+-------------+
|
|
mytable.sql.xz