Dropping an index that turns out to be load-bearing means rebuilding it on a large table under pressure, which is why unused-looking indexes survive for years.
ALTER TABLE orders ALTER INDEX idx_legacy_status INVISIBLE;
-- the optimiser ignores it. writes still maintain it, so making it
-- visible again is instant rather than a rebuild.
ALTER TABLE orders ALTER INDEX idx_legacy_status VISIBLE;
SELECT index_name, is_visible FROM information_schema.statistics
WHERE table_schema = 'shop' AND table_name = 'orders';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 cost is that writes keep paying for it during the trial, so this is evidence-gathering rather than a saving — the drop still has to follow. The primary key cannot be made invisible, which is the one restriction and is obvious in retrospect.