How to Find and Remove Redundant Database Indexes Without Breaking Foreign Keys

A composite index like (import_id, file_path) already covers queries on import_id alone due to B-Tree's leftmost prefix property, making a separate single-column index on import_id redundant. Keeping such duplicate indexes wastes disk space, slows down write operations, and unnecessarily expands the query optimizer's decision space without offering any read benefit. MySQL's built-in sys.schema_redundant_indexes view and Percona's pt-duplicate-key-checker tool can automatically detect these redundant indexes and generate the corresponding DROP statements. However, developers must verify that a foreign key constraint does not solely depend on the index being removed, as dropping such an index triggers ERROR 1553. A safe removal approach involves wrapping the migration in reversible up/down steps and confirming that an existing composite index already satisfies the foreign key's indexing requirement.