Dropping an index that turns out to be load-bearing means rebuilding it on a large table under pressure, so unused-looking indexes accumulate for years because nobody wants to be the one who removed the wrong one.
ALTER TABLE orders ALTER INDEX idx_legacy_status INVISIBLE;
-- the optimiser now ignores it. the index is still maintained,
-- so making it visible again is instant.
ALTER TABLE orders ALTER INDEX idx_legacy_status VISIBLE;
SELECT index_name, is_visible FROM information_schema.statistics
WHERE table_name = 'orders';The cost is that writes still pay for maintaining it while it is invisible, so this is a test rather than a saving — the drop still has to happen afterwards. A week of production traffic with the index invisible is a far stronger signal than reading the slow log, because it covers the query nobody remembered. The primary key cannot be made invisible, which is the one restriction worth knowing.