Dropping an index safely: invisible indexes and the unused-index trap

Every long-lived MySQL schema collects indexes nobody remembers adding. Each one costs write amplification, buffer pool space, and time in every online schema change. Removing one is the risky direction: DROP INDEX is instant, but rebuilding a 400 GB index after you guessed wrong is not. MySQL 8.0 added invisible indexes to make that decision reversible.

What an invisible index is

An invisible index is maintained on every write exactly like a visible one, but the optimizer does not consider it when building plans.

ALTER TABLE orders ALTER INDEX idx_customer_status INVISIBLE;
-- and back
ALTER TABLE orders ALTER INDEX idx_customer_status VISIBLE;

The change is a metadata-only operation, so it does not rebuild the index. It still needs a brief metadata lock on the table, so a long-running transaction on orders can queue it, and everything behind it. Set a short lock_wait_timeout for the session before you run it.

Two limits matter. The primary key cannot be made invisible. And an invisible unique index still enforces uniqueness, so it protects data even while the optimizer ignores it.

Step 1: build the candidate list

The sys schema ships a view over Performance Schema index usage:

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'shop';

Treat the output as a list of suspects, not a verdict. It has three weaknesses:

• The counters reset at server restart. An index with zero reads after nine days of uptime says nothing about the quarterly report.
• The counters are per server. An index used only by queries that run on a replica, or by a reporting host, looks unused on the primary. Collect from every node that serves reads.
• It excludes primary and unique indexes, which is correct, but it also cannot tell you that an index is redundant: a prefix of another index ((a) next to (a, b)) usually shows reads, because the optimizer sometimes picks the shorter one. Check sys.schema_redundant_indexes separately.

Step 2: make it invisible, then wait

Make the candidate invisible and leave it that way for a full business cycle: the longest period at which some query might run. For most products that is a month-end close; for some it is a quarter or a year-end job. Invisible costs you the same write overhead as visible, so the observation window saves nothing in space. What it buys is a one-statement rollback.

While you wait, watch for the signals that an index mattered:

• A change in the top statement digests in performance_schema.events_statements_summary_by_digest, specifically average latency or rows examined for statements touching that table.
• Slow log entries that were not there before.
• Errors from queries that name the index. FORCE INDEX (idx_customer_status) against an invisible index fails with error 1176 instead of falling back, so grep application code and ORM hints for index names before the change.

To test a single query as if the index were visible, without changing anything for other sessions:

SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN SELECT ...;

Step 3: drop it, and keep the definition

When the window passes clean, save the definition first:

SHOW CREATE TABLE orders\G

Then drop it, preferably during a quiet period and through your normal online DDL path. DROP INDEX is an in-place operation, but it is still DDL and takes the same metadata lock. Keep the saved CREATE INDEX statement in the change ticket. If you need the index back, you are doing a real build, which is the cost you were avoiding by testing first.

What this costs and what it does not cover

• Invisible indexes do not reduce write cost or storage. They only de-risk the decision.
• Replicas replicate the visibility change, so a replica cannot be your test bed unless you apply it there first with replication handled deliberately. Prefer to observe on all nodes together.
• Dropping an index also removes its statistics and any histogram-free assumptions the optimizer was making. A plan can change even for queries that never used the index, so compare digests, not just the one query you were worried about.
• On MySQL 5.7 there is no invisible index. The alternative is to capture index usage over a long period and accept a rebuild as your rollback, which is one more reason to finish the 8.x move first.

A short checklist

1. Collect sys.schema_unused_indexes from every node, after uptime covering a full business cycle.
2. Search application code, ORM configuration, and saved queries for the index name.
3. Set lock_wait_timeout, then ALTER INDEX ... INVISIBLE.
4. Watch digest latency and the slow log for one full cycle.
5. Save the definition, then drop.

If your schema has dozens of candidates and nobody can say which workloads touch them, this is a standard part of our performance and health review, delivered as a ranked list with the expected write and space savings for each index.