MySQL – Identifying Unused Indexes in Databases

For anyone managing a MySQL database, optimal performance is always a priority. One of the ways to ensure this is by paying attention to indexes and their utilization. This article will focus on how you can identify unused indexes in your MySQL database, which could potentially slow performance and consume unnecessary space.

Why Should You Remove Unused Indexes?

Unused indexes, although seemingly harmless, can negatively impact your database performance. Every time you perform an INSERT or UPDATE operation, MySQL also has to update the indexes. If you have indexes that are not being used for READ operations, they are just consuming resources without providing any benefit. Removing such indexes can save storage space and improve the performance of write operations.

Finding Unused Indexes

You can identify unused indexes using a SQL query that utilizes the ‘INFORMATION_SCHEMA’ tables. Specifically, ‘STATISTICS’ for general index information and ‘INNODB_METRICS’ for usage metrics.

Here’s a SQL query you can use:

SELECT 
    TABLE_NAME, 
    INDEX_NAME 
FROM 
    INFORMATION_SCHEMA.STATISTICS 
WHERE 
    TABLE_SCHEMA = 'sakila' 
    AND INDEX_NAME NOT IN (
        SELECT 
            DISTINCT INDEX_NAME 
        FROM 
            INFORMATION_SCHEMA.INNODB_METRICS 
        WHERE 
            name LIKE 'index_page_read%' 
            AND COUNT > 0
    );

This query does the following:

• Selects the table name and index name from the ‘STATISTICS’ table in the ‘INFORMATION_SCHEMA’ database. The ‘STATISTICS’ table contains metadata about table indexes.
• Filters for a specific database schema (”sakila” in this case). Replace ”sakila” with the name of your database.
• Excludes indexes that have been used for read operations by checking the ‘INNODB_METRICS’ table. This table contains metrics about InnoDB operations, and in this case, we’re interested in metrics that start with ”index_page_read%” and have a count greater than 0.

The result of this query is a list of indexes in the ”sakila” database that have not been used for any read operations.

A Word of Caution

Before you rush to remove all the unused indexes, note that this query only checks for read operations. If an index is only used for write operations (which is less common but possible), it would be listed as unused by this query. Therefore, ensure to scrutinize the list of unused indexes and verify their lack of utility before dropping them.

Additionally, an index might appear unused because the data or usage patterns have not necessitated its use yet. For example, if you have just created an index or the indexed fields are not often included in WHERE clauses, the index might appear unused. In such cases, you might want to give it more time or analyze your query patterns more thoroughly before removing the index.

Conclusion

Regularly checking for and removing unused indexes is an important part of optimizing your MySQL database. However, it should be done with care to avoid removing indexes that might be necessary for current or future operations. Always remember to back up your database before performing operations that can affect your data or performance.

Here is the link to my Twitter and LinkedIn profile.

Reference: Pinal Dave (https://blog.sqlauthority.com)