MySQL – Find unused indexes

Indexes are used to quickly locate records with particular column values. Without an index, MySQL has to read through the entire table starting with the first entry in order to find the pertinent entries.

The cost increases with the size of the table.

MySQL can quickly decide the location to seek in the midst of the data file without having to look at all the data if the database has an index for the questioned columns. Compared to reading each row sequentially, this is substantially faster.

On the other hand, having unnecessary indexes is troublesome. Ineffective indexes take up space and slow down INSERT, UPDATE, and DELETE searches.

Index utilization is tracked by MySQL as part of its PERFORMANCE SCHEMA. The schema unused indexes view, which lists indexes for which there are no events, can be used to query this data. The absence of events suggests that these indexes may be unnecessary and idle for some time.

Therefore, you can query the following view to get the list of unused indexes.
SELECT * FROM sys.`schema_unused_indexes`;

You can use the following SELECT statement and modify it to suit your needs if you don’t want to rely on the view and want to filter it using your own unique criteria.

SELECT
`t`.`OBJECT_SCHEMA` AS `object_schema`,
`t`.`OBJECT_NAME` AS `object_name`,
`t`.`INDEX_NAME` AS `index_name`
FROM
(
`performance_schema`.`table_io_waits_summary_by_index_usage` `t`
JOIN `information_schema`.`STATISTICS` `s` ON (((
`t`.`OBJECT_SCHEMA` = `information_schema`.`s`.`TABLE_SCHEMA`
)
AND ( `t`.`OBJECT_NAME` = `information_schema`.`s`.`TABLE_NAME` )
AND ( `t`.`INDEX_NAME` = `information_schema`.`s`.`INDEX_NAME` ))))
WHERE
((
`t`.`INDEX_NAME` IS NOT NULL
)
AND ( `t`.`COUNT_STAR` = 0 )
AND ( `t`.`OBJECT_SCHEMA` <> ‘mysql’ )
AND ( `t`.`INDEX_NAME` <> ‘PRIMARY’ )
AND ( `information_schema`.`s`.`NON_UNIQUE` = 1 )
AND ( `information_schema`.`s`.`SEQ_IN_INDEX` = 1 ))
ORDER BY
`t`.`OBJECT_SCHEMA`,
`t`.`OBJECT_NAME`