Bug #86660 schema_redundant_indexes says longer but non-unique index is redundant
Submitted: 12 Jun 2017 16:02 Modified: 12 Jun 2017 16:35
Reporter: Gillian Gunson (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: SYS Schema Severity:S3 (Non-critical)
Version:1.5.1 OS:Any
Assigned to: CPU Architecture:Any

[12 Jun 2017 16:02] Gillian Gunson
Description:
The schema_redundant_indexes view in sys schema will usually say that an index on (a, b) is redundant if there's already an index on (a, b, c), if both indexes are non-unique. Since the first is a prefix of the second. 

However, if the index on (a, b) is UNIQUE and the longer index isn't, it will report the longer (a, b, c) index as redundant. 

If the view was being used to decide what indexes to drop, this could be a performance degradation for queries needing filtering/covering on all 3 columns.

How to repeat:
CREATE TABLE `redundant_indexes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `foo_id` int(11) NOT NULL,
  `bar_id` int(11) NOT NULL,
  `baz` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_foo_id_bar_id` (`foo_id`,`bar_id`),
  KEY `index_foo_id_bar_id_baz` (`foo_id`,`bar_id`,`baz`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> select * from schema_redundant_indexes where table_name = 'redundant_indexes'\G
*************************** 1. row ***************************
              table_schema: ggunson
                table_name: redundant_indexes
      redundant_index_name: index_foo_id_bar_id_baz
   redundant_index_columns: foo_id,bar_id,baz
redundant_index_non_unique: 1
       dominant_index_name: index_foo_id_bar_id
    dominant_index_columns: foo_id,bar_id
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `ggunson`.`redundant_indexes` DROP INDEX `index_foo_id_bar_id_baz`
1 row in set (0.03 sec)

Suggested fix:
Have the view not report the longer index as redundant. 

The code is: https://github.com/mysql/mysql-sys/blob/master/views/i_s/schema_redundant_indexes.sql

The check for uniqueness is 

WHERE
...
(
          (redundant_keys.non_unique > dominant_keys.non_unique)
          OR (redundant_keys.non_unique = dominant_keys.non_unique 
          	AND IF(redundant_keys.index_name='PRIMARY', '', redundant_keys.index_name) > IF(dominant_keys.index_name='PRIMARY', '', dominant_keys.index_name)
          )

non_unique returns 0 for a unique index and 1 for non-unique, so the "(redundant_keys.non_unique > dominant_keys.non_unique)" is where it's reporting as TRUE.
[12 Jun 2017 16:23] Mark Leith
Verified as described - thanks!
[12 Jun 2017 16:35] Gillian Gunson
A workaround for querying schema_redundant_indexes where it doesn't report a longer non-unique index as redundant to a unique index might be:

WHERE ... length(redundant_index_columns) <= length(dominant_index_columns);

On the assumption that *_index_columns is a comma-separated list of the indexes, so the longer index will have the longer string for that column. It's a hack but it'll work in the meantime.