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

Redundant indexes occur when:

• One index fully covers another.

• Composite indexes start with the same columns as smaller indexes.

Examples:

• INDEX (email) is redundant if INDEX (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.