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.