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

http://ift.tt/14B8PqZ

Covering Indexes not being chosen by optimizerI 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 not needed 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 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: indexpossible_keys: CountryCode,conPop          key: CountryCode         <-------- Wrong index      key_len: 3          ref: NULL         rows: 259729        Extra: NULLProblem 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: indexpossible_keys: CountryCode,conPop          key: conPop             <--------- Covering index      key_len: 7          ref: NULL         rows: 259729     filtered: 100.00        Extra: Using index1 row in set, 1 warning (0,00 sec)Great news, the optimizer is now picking the covering index and query is 3x quicker!!

from Planet MySQL http://ift.tt/1du18ol