Identify and Remove Unused MySQL Indexes

Over time, MySQL databases accumulate redundant or unused indexes that degrade write performance and consume unnecessary disk space and memory. This article provides a straightforward guide on how to identify these performance-draining indexes using MySQL’s built-in sys schema and outlines the safe process of removing them to optimize your database performance.

Why Unused and Redundant Indexes Are a Problem

Every time a row is inserted, updated, or deleted in a MySQL table, the database must update all associated indexes. While indexes speed up read queries, having too many of them: * Slows down write operations (INSERT, UPDATE, DELETE). * Consumes valuable RAM (Buffer Pool space) that could be used for caching data. * Increases the size of your database backups.

Step 1: Identify Unused Indexes

Starting with MySQL 5.7 and 8.0, the sys schema is enabled by default. It provides a view called schema_unused_indexes that tracks which indexes have not been used by any queries since the database server last started.

Run the following query to find unused indexes across your schemas:

SELECT 
    object_schema AS 'Database',
    object_name AS 'Table',
    index_name AS 'Unused Index'
FROM 
    sys.schema_unused_indexes
WHERE 
    object_schema NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema');

Note: Because this data is collected since the last MySQL startup, make sure your server has been running for a representative period of time (e.g., days or weeks) so that scheduled cron jobs and rare queries have had a chance to run.

Step 2: Identify Redundant and Duplicate Indexes

A redundant index is one that is a prefix of another index. For example, if you have a composite index on (col1, col2), a separate index on just (col1) is redundant because MySQL can use the composite index to search for col1.

To find duplicate and redundant indexes, query the sys.schema_redundant_indexes view:

SELECT 
    table_schema AS 'Database',
    table_name AS 'Table',
    redundant_index_name AS 'Redundant Index',
    dominant_index_name AS 'Dominant Index'
FROM 
    sys.schema_redundant_indexes;

Step 3: Safely Remove the Indexes

Before permanently deleting an index, it is best practice to make the index invisible first. Invisible indexes are maintained by write operations but are ignored by the MySQL optimizer during query execution. This allows you to test the performance impact of removing the index without actually deleting it.

1. Make the Index Invisible (MySQL 8.0+)

If you are running MySQL 8.0, run the following command to hide the index:

ALTER TABLE table_name ALTER INDEX index_name INVISIBLE;

Monitor your application performance and slow query logs for a few days. If queries slow down, you can instantly restore the index by making it visible again:

ALTER TABLE table_name ALTER INDEX index_name VISIBLE;

2. Drop the Index

Once you are confident that the index is no longer needed, or if you are running an older version of MySQL that does not support invisible indexes, permanently delete the index using:

ALTER TABLE table_name DROP INDEX index_name;