Detecting Unused Indexes

In this lesson, you will learn how to identify and prune redundant index definitions by auditing system metrics to keep your database performant and lean.

Every index you create adds overhead to your write operations (INSERT, UPDATE, DELETE) because the database must keep the index structure in sync with the table data. Over time, as application requirements evolve, many indexes that were once critical may become "dead weight."

In the MySQL and MariaDB ecosystems, the Performance Schema is your primary tool for detecting these unused indexes. Specifically, the sys schema provides a pre-computed view that makes this audit trivial.

Auditing with the sys Schema

You can query the sys.schema_unused_indexes view to see a list of indexes that the optimizer has not touched since the last server restart:

SELECT 
    object_schema, 
    object_name, 
    index_name 
FROM sys.schema_unused_indexes 
WHERE object_schema NOT IN ('performance_schema', 'mysql', 'sys');

Important Pruning Strategy

Before you drop an index, you must be cautious. Because these metrics reset upon a service restart, an index might appear "unused" simply because the server hasn't been up long enough to run your quarterly reports or annual batch jobs.

To safely prune:

1. Gather Data: Ensure your server has been running through a full business cycle (at least a month) before auditing.
2. Invisible Indexes: Instead of immediately running DROP INDEX, use the ALTER TABLE ... ALTER INDEX ... INVISIBLE command (introduced in MySQL 8.0). Marking an index as invisible tells the optimizer to ignore it without actually removing the data structure. If the application doesn't crash or slow down after a few days of observation, it is safe to proceed with the DROP.
3. Verify: Check your EXPLAIN plans after making an index invisible to ensure no critical queries suddenly revert to a full table scan.

By regularly auditing your schema and removing unused indexes, you reduce disk I/O and boost write throughput, ensuring your database only spends resources on structures that actually improve query speed.

Key Takeaway: Use the sys.schema_unused_indexes view to identify candidates, but always use Invisible Indexes as a safety buffer before dropping them from production.