With latest version of MySQL (5.7.5) the optimizer picks covering index before non-covering indexes (Bug #18035906)

Covering Indexes not being chosen by optimizer

I noticed this problem about a year ago when writing this blogpost. In short problem is when adding a covering index and keeping old non-covering index the optimizer opted to use old non-covering index. Only solution was to FORCE optimizer to use covering index which meant you needed to modify your DML or remove old index.

Using the same test setup as in my old blogpost but when you add new covering index do not drop the old index.

So, instead of running:

mysql> ALTER TABLE big DROP INDEX CountryCode;
mysql> ALTER TABLE big ADD INDEX conPop (CountryCode, Population);

We run only statement for adding new covering index and do not remove old index:

mysql> ALTER TABLE big ADD INDEX conPop (CountryCode, Population);

With MySQL 5.6 you will see the following output from EXPLAIN:

mysql> EXPLAIN SELECT CountryCode, SUM(Population) from big group by CountryCode\G
           id: 1
  select_type: SIMPLE
        table: big
         type: index
possible_keys: CountryCode,conPop
          key: CountryCode         <-------- Wrong index
      key_len: 3
          ref: NULL
         rows: 259729
        Extra: NULL

Problem is that optimizer is not using our new covering index even though it would be the fastest way to access data. Only solution is to drop the old index or add keyword FORCE INDEX to your SQL.

With latest version of MySQL (MySQL 5.7.5) there is a small release note here stating: "If the optimizer chose to perform an index scan, in some cases it could choose a noncovering rather than a covering index. (Bug #18035906)".

Lets see output from EXPLAIN using the same scenario as described above with MySQL 5.7.5:

mysql> EXPLAIN SELECT CountryCode, SUM(Population) from big group by CountryCode\G
           id: 1
  select_type: SIMPLE
        table: big
   partitions: NULL
         type: index
possible_keys: CountryCode,conPop
          key: conPop             <--------- Covering index
      key_len: 7
          ref: NULL
         rows: 259729
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0,00 sec)

Great news, with MySQL 5.7.5 the optimizer is now picking the covering index and query is 3x quicker!!