How do you identify and remove unused or redundant indexes without affecting application performance?

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.

1. How to Identify Unused Indexes

1
a. Using performance_schema.table_io_waits_summary_by_index_usage
2
• Shows how many times MySQL used each index.
3
• Indexes with zero or extremely low usage may be candidates for removal.
4
5
Example Query:
6
7
SELECT *
8
FROM performance_schema.table_io_waits_summary_by_index_usage
9
WHERE index_name IS NOT NULL
10
ORDER BY COUNT_STAR ASC;
11
12
13
b. Checking EXPLAIN Plans
14
• Run EXPLAIN on frequent queries to see which indexes MySQL actually uses.
15
16
c. Using MySQL Enterprise Monitor / Percona Toolkit (pt-index-usage)
17
• Analyzes query logs to detect indexes that are never used.
18
• Very accurate for real workloads.

2. Detecting Redundant Indexes

1
Redundant indexes occur when:
2
• One index fully covers another.
3
• Composite indexes start with the same columns as smaller indexes.
4
5
Examples:
6
• INDEX (email) is redundant if INDEX (email, status) exists.
7
• Duplicate indexes created by mistake.
8
9
Query to detect redundant indexes (information_schema):
10
11
SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX
12
FROM information_schema.STATISTICS
13
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;
14

3. How to Test Removal Without Dropping the Index

1
Use Invisible Indexes (MySQL 8.0)
2
Make the index invisible so the optimizer ignores it:
3
4
ALTER INDEX idx_email INVISIBLE;
5
6
Monitor system performance:
7
• Slow queries?
8
• Missing index warnings?
9
• Query plans changed?
10
11
If everything works fine, safely drop the index.

4. How to Safely Remove the Index

1
Drop the index only after testing:
2
3
ALTER TABLE users DROP INDEX idx_email;
4
5
6
Before dropping:
7
• Confirm index usage is very low or zero.
8
• Ensure EXPLAIN plans do not rely on it.
9
• Validate application logs for performance changes.
10
• Keep backups for rollback.

5. Best Practices

1
Avoid creating overlapping composite indexes.
2
Audit indexes periodically (especially on large or high-write tables).
3
Prefer composite indexes over many single-column indexes.
4
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.

Difficulty: 6/10

Topics: index usage analysis, performance impact assessment, schema migration safety

Scenario Questions

0-2 years experience

1
You notice a table with 10 indexes — how would you figure out which ones aren't being used at all, and what steps would you take before removing one?
2
What happens if you drop an index that's not in any query plan but the app team says it's 'needed for future features' — what do you do?

2-5 years experience

1
After a recent deploy, our search latency doubled — we just removed an index we thought was unused. How do you debug this?
2
Your team wants to remove three indexes to save disk space, but the DBA says they might be used in rare joins. How do you validate this without breaking production?

5-8 years experience

1
You're optimizing a 2TB orders table with 15 indexes — how do you prioritize which ones to remove when you can't afford to test every combination in production?
2
How would you design a process to automatically flag and deprecate unused indexes across 50+ microservices without causing silent failures?

8+ years experience

1
We're migrating from MySQL 5.7 to 8.0 and inherited 200+ legacy indexes — how do you architect a multi-quarter plan to clean them up without disrupting 10+ teams?
2
How would you enforce a policy around index hygiene at scale when different teams own different schemas and have conflicting priorities?

Follow-up Questions

❓How would you convince a teammate not to drop an index they think is unused?
❓What if the index is only used once a month during a batch job?
❓How do you handle this in a high-traffic read-heavy system with no downtime window?