Description:
In 'index' queries that do e.g. GROUP BY or DISTINCT, like this one:
select distinct b from t1 where c not in (0) and d > 0 order by b;
Optimizer will be dropping valuable index properties in "reconsidering_access_paths_for_index_ordering" step and pick one "randomly" (simply the first one that is table to do a GROUP BY or DISTINCT, rather than one that is covering).
So, if indexes are defined:
I1: (groupby_field, filter_field_b),
I2: (groupby_field, filter_field_a, filter_field_b)
A worse index will be chosen (one that cannot filter by one of the fields and is non-covering). Swapping these two indexes will give you correct behavior.
One can force or ignore good/bad indexes, of course, or change the table definition to have better performing index ahead of bad-performing index.
We've seen 100x query performance drop from this.
How to repeat:
clowntown> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=49138 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
clowntown> explain select distinct b from t1 where c not in (0) and d > 0;
+----+-------------+-------+------+---------------+------+---------+------+-------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+------------------------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 32487 | Using where; Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+-------+------------------------------+
1 row in set (0.00 sec)
clowntown> alter table t1 add index non_covering_index (b, d);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
clowntown> explain select distinct b from t1 where c not in (0) and d > 0;
+----+-------------+-------+-------+--------------------+--------------------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+--------------------+--------------------+---------+------+-------+-------------+
| 1 | SIMPLE | t1 | index | non_covering_index | non_covering_index | 9 | NULL | 32487 | Using where |
+----+-------------+-------+-------+--------------------+--------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)
clowntown> alter table t1 add index covering_index (b, c, d);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
clowntown> explain select distinct b from t1 where c not in (0) and d > 0;
+----+-------------+-------+-------+-----------------------------------+--------------------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------------------+--------------------+---------+------+-------+-------------+
| 1 | SIMPLE | t1 | index | non_covering_index,covering_index | non_covering_index | 9 | NULL | 32487 | Using where |
+----+-------------+-------+-------+-----------------------------------+--------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)
clowntown> alter table t1 drop key non_covering_index;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
clowntown> explain select distinct b from t1 where c not in (0) and d > 0;
+----+-------------+-------+-------+----------------+----------------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+----------------+---------+------+-------+--------------------------+
| 1 | SIMPLE | t1 | index | covering_index | covering_index | 14 | NULL | 32487 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)
clowntown> alter table t1 add index non_covering_index (b, d);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
clowntown> explain select distinct b from t1 where c not in (0) and d > 0;
+----+-------------+-------+-------+-----------------------------------+----------------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------------------+----------------+---------+------+-------+--------------------------+
| 1 | SIMPLE | t1 | index | covering_index,non_covering_index | covering_index | 14 | NULL | 32487 | Using where; Using index |
+----+-------------+-------+-------+-----------------------------------+----------------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)
clowntown> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`a`),
KEY `covering_index` (`b`,`c`,`d`),
KEY `non_covering_index` (`b`,`d`)
) ENGINE=InnoDB AUTO_INCREMENT=49138 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Suggested fix:
8.0 cost model is different ant doesn't step on this.
In earlier versions optimizer should keep the context (selectivity, coverage, etc) to the final 'reconsidering' step.
Description: In 'index' queries that do e.g. GROUP BY or DISTINCT, like this one: select distinct b from t1 where c not in (0) and d > 0 order by b; Optimizer will be dropping valuable index properties in "reconsidering_access_paths_for_index_ordering" step and pick one "randomly" (simply the first one that is table to do a GROUP BY or DISTINCT, rather than one that is covering). So, if indexes are defined: I1: (groupby_field, filter_field_b), I2: (groupby_field, filter_field_a, filter_field_b) A worse index will be chosen (one that cannot filter by one of the fields and is non-covering). Swapping these two indexes will give you correct behavior. One can force or ignore good/bad indexes, of course, or change the table definition to have better performing index ahead of bad-performing index. We've seen 100x query performance drop from this. How to repeat: clowntown> show create table t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=49138 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) clowntown> explain select distinct b from t1 where c not in (0) and d > 0; +----+-------------+-------+------+---------------+------+---------+------+-------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 32487 | Using where; Using temporary | +----+-------------+-------+------+---------------+------+---------+------+-------+------------------------------+ 1 row in set (0.00 sec) clowntown> alter table t1 add index non_covering_index (b, d); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 clowntown> explain select distinct b from t1 where c not in (0) and d > 0; +----+-------------+-------+-------+--------------------+--------------------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+--------------------+--------------------+---------+------+-------+-------------+ | 1 | SIMPLE | t1 | index | non_covering_index | non_covering_index | 9 | NULL | 32487 | Using where | +----+-------------+-------+-------+--------------------+--------------------+---------+------+-------+-------------+ 1 row in set (0.00 sec) clowntown> alter table t1 add index covering_index (b, c, d); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 clowntown> explain select distinct b from t1 where c not in (0) and d > 0; +----+-------------+-------+-------+-----------------------------------+--------------------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-----------------------------------+--------------------+---------+------+-------+-------------+ | 1 | SIMPLE | t1 | index | non_covering_index,covering_index | non_covering_index | 9 | NULL | 32487 | Using where | +----+-------------+-------+-------+-----------------------------------+--------------------+---------+------+-------+-------------+ 1 row in set (0.00 sec) clowntown> alter table t1 drop key non_covering_index; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 clowntown> explain select distinct b from t1 where c not in (0) and d > 0; +----+-------------+-------+-------+----------------+----------------+---------+------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+-------+--------------------------+ | 1 | SIMPLE | t1 | index | covering_index | covering_index | 14 | NULL | 32487 | Using where; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+-------+--------------------------+ 1 row in set (0.00 sec) clowntown> alter table t1 add index non_covering_index (b, d); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 clowntown> explain select distinct b from t1 where c not in (0) and d > 0; +----+-------------+-------+-------+-----------------------------------+----------------+---------+------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-----------------------------------+----------------+---------+------+-------+--------------------------+ | 1 | SIMPLE | t1 | index | covering_index,non_covering_index | covering_index | 14 | NULL | 32487 | Using where; Using index | +----+-------------+-------+-------+-----------------------------------+----------------+---------+------+-------+--------------------------+ 1 row in set (0.00 sec) clowntown> show create table t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`a`), KEY `covering_index` (`b`,`c`,`d`), KEY `non_covering_index` (`b`,`d`) ) ENGINE=InnoDB AUTO_INCREMENT=49138 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Suggested fix: 8.0 cost model is different ant doesn't step on this. In earlier versions optimizer should keep the context (selectivity, coverage, etc) to the final 'reconsidering' step.