There is an incident shape every MySQL operator eventually sees. Someone audits a table, finds an index that "nothing uses," drops it, and everything is fine for three weeks. Then the month-end billing job runs — the one query family that used the index — and a report that finished in minutes is now projected to finish on Thursday. The drop took milliseconds. The rebuild takes six hours on a table that size, during which the job is either down or eating the primary alive with table scans.
The asymmetry is the whole problem: DROP INDEX is instant and cheap, ADD INDEX is slow and expensive, and the information you needed — does anything actually use this? — only reveals itself over a full business cycle. MySQL 8.0's invisible indexes fix the asymmetry. These are my notes on using them as a standard part of index hygiene rather than a curiosity.
What an invisible index actually is
An invisible index is fully maintained but ignored by the optimizer. Every INSERT, UPDATE, and DELETE keeps it current, it occupies the same disk space, and its statistics stay live. The only change is that the optimizer will not consider it when planning queries. Toggling visibility is a metadata-only operation — effectively instant in both directions, which is the entire point:
-- Rehearse the drop: instant, reversible.
ALTER TABLE orders ALTER INDEX idx_orders_customer_created INVISIBLE;
-- The panic button: also instant.
ALTER TABLE orders ALTER INDEX idx_orders_customer_created VISIBLE;Compare the two failure recoveries. If you dropped the index and were wrong, recovery is ADD INDEX: hours of build time on a large table, I/O pressure while it runs, and an angry batch job in the meantime. If you made it invisible and were wrong, recovery is one metadata flip and the next execution of the query plans normally. Same experiment, radically different blast radius.
The cost of the rehearsal period is honest to state: while invisible, the index keeps its write overhead and disk footprint. You are paying full maintenance price for zero read benefit, deliberately, for a bounded observation window. That is the premium on the insurance.
The rules and edge cases worth knowing
A few sharp edges before you rely on this. The primary key cannot be made invisible — and that extends further than people expect: if a table has no explicit primary key, InnoDB promotes the first unique index on NOT NULL columns to act as the clustered key, and that index cannot be made invisible either. Unique invisible indexes still enforce uniqueness; visibility affects planning, not constraints, so hiding a unique index does not rehearse losing its duplicate protection. And index hints interact loudly: a query that names an invisible index in FORCE INDEX or USE INDEX fails with an error, which is actually a feature — it flushes out hard-coded hint dependencies you forgot existed.
One more planning nuance: making an index invisible can change plans for queries that never "used" it in the index-dive sense, because index existence feeds optimizer statistics and access-path choices. That is an argument for the rehearsal, not against it — it is precisely the class of effect a metadata-only experiment reveals safely.
use_invisible_indexes: testing in both directions
The optimizer switch use_invisible_indexes (default off) lets a single session opt in to seeing invisible indexes. This unlocks the reverse experiment: verify what a plan would look like with the index, without exposing the whole workload to it.
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN FORMAT=TREE
SELECT customer_id, SUM(total)
FROM orders
WHERE customer_id = 18234
AND created_at >= '2026-01-01'
GROUP BY customer_id;
SET SESSION optimizer_switch = 'use_invisible_indexes=off';Two workflows fall out of this. When retiring an index, flip it invisible and use a session with the switch on to confirm what the plan was before, so you can compare against the plan the fleet now gets. When adding a risky index, create it invisible first, verify plans session-by-session with the switch, then make it visible fleet-wide in a single instant step once you trust it. Index rollout with a rollback lever is a much calmer way to ship than CREATE INDEX straight into production traffic.
Remember that visibility changes replicate like any other DDL, so a flip on the primary changes plans on every replica too. If read replicas serve a different query mix than the primary — reporting queries are the classic case — evaluate candidates against the replica workload as well, not just the primary's. An index that is dead weight on the primary can be the only thing keeping a replica-side dashboard query off a full scan, and per-server usage statistics are the only way to know.
Finding candidates: the index audit workflow
Invisible indexes make the last step of an index audit safe, but you still need the first steps. My workflow:
- 1. Pull usage data. performance_schema tracks per-index I/O; the sys schema wraps it in sys.schema_unused_indexes, which lists indexes with zero read events since the server started. Check uptime first — a server restarted last week has not seen month-end yet, and "unused for six days" is not evidence.
- 2. Exclude structural indexes. Unique indexes enforcing business constraints and any index MySQL requires stay, regardless of read counts. In InnoDB, foreign keys require a usable index on the referencing columns, so FK-supporting indexes are not drop candidates while the constraint exists.
- 3. Check for redundancy. An index on (a) is usually redundant next to (a, b); sys.schema_redundant_indexes finds these. Redundant indexes are the safest drops because the survivor covers the same queries.
- 4. Flip candidates invisible. One at a time on hot tables, batched on quiet ones, each with a ticket recording the revert command.
- 5. Wait a full business cycle. Not a sprint — a cycle. Month-end close, quarterly reporting, the annual compliance export. The billing-job incident at the top of this post happens precisely when the observation window is shorter than the workload's real period.
- 6. Watch, then drop. Monitor slow-query counts, table scan rates, and latency on the affected tables through the window. Clean cycle: drop the index and reclaim the write overhead and space. Any regression: flip it visible, done in milliseconds.
Steps 5 and 6 are monitoring problems, not SQL problems. If you cannot see per-query latency shifts and scan-rate changes on the affected tables, the safety net has no one watching it. And note the interaction with schema-change planning from MySQL online DDL vs PostgreSQL DDL locks: the final DROP INDEX is an INPLACE metadata operation and cheap, but any accidental rebuild-and-recreate cycle is not, which is exactly why the rehearsal step earns its keep.
Why not just keep every index?
Because unused indexes are not free. Every secondary index adds write amplification to inserts, updates on indexed columns, and page splits under load. Indexes inflate the buffer pool working set, pushing hot data out of memory. They slow the table-rebuilding ALTERs discussed above in proportion to how many trees must be rebuilt. On a write-heavy table, retiring two dead indexes is often a real, measurable win — I will not put a universal number on it because it depends entirely on the workload, which is precisely why the audit-and-observe loop beats folklore in both directions.
Where MonPG fits
Straight positioning, as always: MonPG is a PostgreSQL monitoring platform. MySQL support is coming soon and does not exist in the product today. The index-audit loop in this post is one of the concrete workflows driving that build — usage data, per-query latency baselines, and regression detection across a business cycle are what make invisible-index rehearsals trustworthy, and that is what the MySQL monitoring (coming soon) product is being designed around. If your team also operates PostgreSQL, the equivalent index-usage and query-regression evidence is already shipping there — you can start there while the MySQL work lands.