Identifying and Safely Removing Unused or Redundant Indexes in MySQL
Unused or redundant indexes waste storage, slow down write operations, and consume memory. MySQL provides several tools and techniques to safely identify and remove unnecessary indexes without impacting application performance.
a. Using
performance_schema.table_io_waits_summary_by_index_usage• Shows how many times MySQL used each index.
• Indexes with zero or extremely low usage may be candidates for removal.
Example Query:
SELECT *
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
ORDER BY COUNT_STAR ASC;
b. Checking EXPLAIN Plans
• Run EXPLAIN on frequent queries to see which indexes MySQL actually uses.
c. Using MySQL Enterprise Monitor / Percona Toolkit (
pt-index-usage)• Analyzes query logs to detect indexes that are never used.
• Very accurate for real workloads.
Redundant indexes occur when:
• One index fully covers another.
• Composite indexes start with the same columns as smaller indexes.
Examples:
•
INDEX (email)is redundant ifINDEX (email, status)exists.• Duplicate indexes created by mistake.
Query to detect redundant indexes (information_schema):
SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX
FROM information_schema.STATISTICS
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;
Use Invisible Indexes (MySQL 8.0)
Make the index invisible so the optimizer ignores it:
ALTER INDEX idx_email INVISIBLE;
Monitor system performance:
• Slow queries?
• Missing index warnings?
• Query plans changed?
If everything works fine, safely drop the index.
Drop the index only after testing:
ALTER TABLE users DROP INDEX idx_email;
Before dropping:
• Confirm index usage is very low or zero.
• Ensure EXPLAIN plans do not rely on it.
• Validate application logs for performance changes.
• Keep backups for rollback.
Avoid creating overlapping composite indexes.
Audit indexes periodically (especially on large or high-write tables).
Prefer composite indexes over many single-column indexes.
Use invisible indexes for safety when testing index removal.
By carefully analyzing index usage and testing changes with invisible indexes, you can safely remove redundant or unused indexes while protecting application performance.