Bug #94224 [5.6] Optimizer reconsiders index based on index definition order, not value
Submitted: 6 Feb 16:45 Modified: 7 Feb 8:43
Reporter: Domas Mituzas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6, 5.6.43 OS:Any (any)
Assigned to: CPU Architecture:Any (any)

[6 Feb 16:45] Domas Mituzas
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.
[7 Feb 8:43] Umesh Shastry
Hello Domas,

Thank you for the report and feedback.

regards,
Umesh