Description:
A regression in index merge sometime after 5.0.22 leads to test case execution time go from 0.06 seconds to 0.17 seconds. Tests were performed on an in memory dataset. On larger sets of data, this performance drop is even more noticeable.
How to repeat:
In 5.0.22:
11:48 test> SELECT SQL_NO_CACHE count(*) FROM t1 WHERE id_two=7 AND id_three=0 AND id_five=1;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.06 sec)
11:48 test> SELECT SQL_NO_CACHE count(*) FROM t1 FORCE INDEX (covering_index) WHERE id_two=7 AND id_three=0 AND id_five=1;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.07 sec)
In 5.0.52:
11:51 test> SELECT SQL_NO_CACHE count(*) FROM t1 WHERE id_two=7 AND id_three=0 AND id_five=1;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.17 sec)
11:51 test> SELECT SQL_NO_CACHE count(*) FROM t1 FORCE INDEX (covering_index) WHERE id_two=7 AND id_three=0 AND id_five=1;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.07 sec)
11:57 test> EXPLAIN SELECT SQL_NO_CACHE count(*) FROM t1 WHERE id_two=7 AND id_three=0 AND id_five=1;
+----+-------------+-------+-------------+----------------------------------------+-------------------------+---------+------+-------+--------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+----------------------------------------+-------------------------+---------+------+-------+--------------------------------------------------------------------+
| 1 | SIMPLE | t1 | index_merge | id_two,id_three,id_five,covering_index | id_five,id_two,id_three | 4,4,4 | NULL | 11157 | Using intersect(id_five,id_two,id_three); Using where; Using index |
+----+-------------+-------+-------------+----------------------------------------+-------------------------+---------+------+-------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
11:57 test> EXPLAIN SELECT SQL_NO_CACHE count(*) FROM t1 FORCE INDEX (covering_index) WHERE id_two=7 AND id_three=0 AND id_five=1;
+----+-------------+-------+------+----------------+----------------+---------+-------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------+----------------+---------+-------------+-------+--------------------------+
| 1 | SIMPLE | t1 | ref | covering_index | covering_index | 8 | const,const | 79007 | Using where; Using index |
+----+-------------+-------+------+----------------+----------------+---------+-------------+-------+--------------------------+
1 row in set (0.00 sec)
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`id_two` int(11) NOT NULL,
`id_three` int(11) NOT NULL,
`id_four` int(11) NOT NULL,
`id_five` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `id_two` (`id_two`),
KEY `id_three` (`id_three`),
KEY `id_five` (`id_five`),
KEY `covering_index` (`id_two`,`id_three`,`id_four`,`id_five`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Suggested fix:
Either improve performance, or choose the other index over index merge in the attached test case.
