Have you ever been looking through a table's indexes and spotted a pair like this sitting side by side?
KEY `idx_logs_on_import_id` (`import_id`)
UNIQUE KEY `idx_logs_on_import_id_and_file` (`import_id`, `file_path`)
Spoiler: you can usually drop the single-column one on top. In this post I'll walk through "why you can drop it," "whether you really should (and the exception where you can't)," "how to find these mechanically," and "how to remove them safely."
Why the single (import_id) index is redundant
The key is a property of B-Tree indexes: the "leftmost prefix" of a composite index also works as a single-column index.
An index on (import_id, file_path) is sorted first by import_id, and then by file_path within that. Think of a phone book sorted by "last name → first name."
- "Look up by last name only" → works (it's sorted from the left)
- "Look up by first name only" → doesn't work
In the same way, (import_id, file_path) gives you:
-
WHERE import_id = ?→ uses the index (the leftmost column isimport_id) -
WHERE file_path = ?→ doesn't (the second column can't be used on its own)
So a WHERE import_id = ? lookup is fully covered by the composite index. The single (import_id) index does exactly the same job as the composite one.
Redundant indexes actually hurt
You might think, "If it's just unused, what's the harm?" But indexes aren't free.
- Slower writes: every INSERT / UPDATE / DELETE has to update every index. That one redundant index adds wasted update cost every single time.
- Eats disk and memory: an index exists as its own structure, separate from the actual data. It also puts pressure on the buffer pool.
- Gives the optimizer pointless extra options: more candidates to consider when building an execution plan.
Zero benefit for reads (the composite covers it), but you keep paying the write cost. That's why redundant indexes should go.
How to find them mechanically
Checking every table by eye isn't realistic. Let the tools do it.
1. MySQL's built-in view sys.schema_redundant_indexes (recommended, nothing to install)
On MySQL 5.7 or later, it works with no extra installation. It detects leftmost-prefix duplicates and even generates the SQL to drop them.
SELECT
table_name,
redundant_index_name, -- the redundant one (candidate to drop)
redundant_index_columns,
dominant_index_name, -- the one that covers it (keep this)
dominant_index_columns,
sql_drop_index -- a DROP statement you can run as-is
FROM sys.schema_redundant_indexes
WHERE table_name = 'logs';
Sample output:
redundant_index_name : idx_logs_on_import_id
dominant_index_name : idx_logs_on_import_id_and_file
sql_drop_index : ALTER TABLE `app`.`logs` DROP INDEX `idx_logs_on_import_id`
2. pt-duplicate-key-checker (Percona Toolkit / CLI)
The go-to tool that scans the whole DB at once and spits out duplicate/redundant indexes along with DROP statements. Easy to plug into CI or a scheduled job.
pt-duplicate-key-checker --host 127.0.0.1 --user readonly --ask-pass
3. sys.schema_unused_indexes (finding unused indexes)
On a different axis from "redundant," this lists indexes that "haven't been used by a single query since the server last started." But see the FK caveat below.
4. Tools that come with your ORM (e.g. Rails' active_record_doctor)
From the app side, you can detect detect_extraneous_indexes (redundant) and detect_unindexed_foreign_keys (FKs without an index). Since it runs in the same language as your migrations, it plays nicely with CI.
[Important] Redundant candidates you must NOT drop: foreign key (FK) indexes
Even if a tool says "unused" or "candidate," some indexes will throw an error if you drop them. Those are foreign key indexes.
Whenever a parent row is deleted or updated, MySQL checks every time whether any child rows still reference it. That's essentially a WHERE fk_column = ? lookup, and without an index it's a full scan every time — painfully slow. So MySQL requires that "if you add an FK, the child column must have an index," and creates one automatically if there isn't one.
-- Even if this looks "unused," it's required for the FK. Dropping it gives ERROR 1553.
KEY `fk_logs_on_user_id` (`user_id`),
CONSTRAINT `fk_logs_on_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
How to tell: is there another index that has that FK column as its leftmost column?
- If yes (e.g. a composite index whose leftmost column is the FK column) → that one satisfies the FK requirement, so the single-column index can go.
- If no → that index is the FK's only lifeline → you can't drop it (dropping it gives
ERROR 1553: Cannot drop index ... needed in a foreign key constraint).
In the example at the top, the single (import_id) can be dropped because the composite (import_id, file_path) has import_id as its leftmost column, so it can take over the FK index requirement too.
How to remove them safely
Migration
It's just dropping one index. Keep it reversible (a rollback brings it back).
class RemoveRedundantIndexFromLogs < ActiveRecord::Migration[8.0]
def change
remove_index :logs, column: :import_id, name: 'idx_logs_on_import_id'
end
end
In plain SQL:
ALTER TABLE `logs` DROP INDEX `idx_logs_on_import_id`;
Before applying, confirm for real that it can be dropped
Especially for columns involved in an FK, even if on paper you think "the composite should cover it," it's reassuring to actually run the DROP once and make sure ERROR 1553 doesn't show up. Run up → down on your dev DB and confirm you can roll it back, too.
# Check that the DROP actually goes through (no FK error)
bin/rails db:migrate:up VERSION=<version>
# Once confirmed, roll it back (keep the dev DB clean)
bin/rails db:migrate:down VERSION=<version>
If up goes through without ERROR 1553, that's rock-solid proof that "the composite index satisfies the FK index requirement."
Top comments (0)