Skip to content

CONCEPT Cited by 2 sources

MySQL invisible index

A MySQL invisible index is a secondary index that physically exists and is maintained on every write, but is hidden from the query optimiser — so no query plan will use it. Introduced in MySQL 8.0, invisible indexes are the canonical reversible drop-test primitive: a way to measure "what would happen to query performance if I dropped this index?" without actually dropping it.

ALTER TABLE your_table_name ALTER INDEX your_index_name INVISIBLE;
-- Run production workload, measure affected queries.
-- If performance degrades, flip back:
ALTER TABLE your_table_name ALTER INDEX your_index_name VISIBLE;
-- If performance is fine, commit the drop:
ALTER TABLE your_table_name DROP INDEX your_index_name;

(Source: sources/2026-04-21-planetscale-what-are-the-disadvantages-of-database-indexes)

Why it exists

Dropping an index is destructive and hard to reverse under production workloads: recreating a dropped index on a large table can take hours and place heavy load on the server. If you drop an index and discover a critical query was silently relying on it, the recovery window is long.

Invisible indexes invert the risk profile by decoupling the decision from the destructive act:

  • Drop candidate chosen (e.g. via the information_schema.STATISTICS cardinality = 0 heuristic or production query-level telemetry).
  • Flip invisible — zero-cost metadata change.
  • Measure — run production workload; watch for latency regressions, slow-query-log entries, rows-read jumps.
  • Decide: if invisible-state performance is acceptable, DROP INDEX is safe; if any query regresses, flip back to visible and the system is restored instantly.

The invisibility window is the reversible staging ground between "I think we can drop this" and the irreversible act.

Mechanics

  • Invisible indexes remain maintained on every write — INSERT/UPDATE/DELETE still walk the index B+tree. Making an index invisible does not eliminate its write-amplification cost — it eliminates only its read-path benefit. This is the key difference from drop.
  • Storage cost preserved — the B+tree still exists on disk.
  • Query optimiser blind — EXPLAIN will not show the invisible index in possible_keys.
  • Metadata-only ALTER — the flip is instant; no table rebuild, no replica lag.
  • SET optimizer_switch='use_invisible_indexes=on' is a session-scoped escape hatch that lets DBAs query as if the index were visible, for pre-flip planner analysis. (Not surfaced in the Lien post; MySQL 8.0 native feature.)
  • Primary indexes cannot be made invisible. Only secondary indexes are eligible.

Invisibility vs drop — comparison

Property Visible Invisible Dropped
B+tree exists on disk Yes Yes No
Storage cost Yes Yes No
Write amplification Yes Yes No
Queries can use it Yes No No
Reversible n/a Yes (instant flip) No (recreate required)
Metadata-only ALTER n/a Yes Yes (but free space reclamation can be slow)

PlanetScale operational integration

On PlanetScale specifically, invisible-index DDL routes through the deploy request workflow by default — "we don't allow direct DDL on production branches, unless they have safe migrations disabled (not recommended)" — with the redemption that PlanetScale's Revert feature lets operators "simply click the 'Revert' button if you decide you want to undo an altered or dropped index and it will be reverted near instantaneously" (Source: sources/2026-04-21-planetscale-what-are-the-disadvantages-of-database-indexes).

The invisible-index mechanism is therefore doubly reversible on PlanetScale: (1) the native MySQL visibility flip, plus (2) PlanetScale's instant schema revert of the schema-change deploy itself — two independent levels of safety net for index lifecycle decisions.

Distinction from invisible columns

MySQL's invisible columns (same "invisible" terminology, different primitive) are a SELECT * / INSERT INTO t VALUES (…) discovery mechanism for deprecating columns — the column physically exists and explicit-column SQL still accesses it, but default-column expansion skips it. Invisible columns are a discovery primitive for "who is still reading this column?"; invisible indexes are a validation primitive for "what happens to queries if I drop this index?"

Both are MySQL-8.0 reversible-staging mechanisms for destructive operations (DROP COLUMN / DROP INDEX), but they work at different altitudes:

Invisible column Invisible index
Hides from SELECT * expansion Query optimiser
Explicit name still works Yes No (optimiser ignores it)
Write cost preserved Yes Yes
Purpose Find SELECT * callers Simulate drop safely
Pre-drop stage Step 6 of expand-migrate-contract Validation before DROP INDEX

Anti-patterns

  • Indefinite invisibility. The point is to measure quickly and either commit (DROP INDEX) or revert (VISIBLE). Leaving the candidate invisible for weeks pays the storage + write-amplification cost without the read benefit — worst of all worlds.
  • Invisibility as throttle. Using invisibility to "temporarily disable" an index during a load spike is not what it's for — the index still costs write amplification during the invisibility window. If you want to throttle write load, you need to actually drop the indexes (and pay the recreate cost) or restructure the workload.
  • Single-point measurement. One-hour invisible window on a workload that has daily/weekly/monthly peaks will miss the batch job or quarterly report that uses the index. Measure across at least one full workload cycle.
  • Invisibility without slow_query_log monitoring. The invisible-state measurement is only as good as the instrumentation. If slow-query captures are off, the validation window can pass without detecting a latent p99.9 regression on a rare query.

Seen in

  • sources/2026-04-21-planetscale-why-isnt-mysql-using-my-index — Aaron Francis (PlanetScale, 2023-05-04) names invisible indexes as reason #7 in his eight-reason taxonomy of why MySQL silently ignores an index. The framing is diagnostic: "This is perhaps the least common reason your index wouldn't be used, but it is possible!" Read SHOW INDEXES Visible column before blaming more subtle causes (selectivity, stats, leftmost-prefix misses, obfuscation). Pairs with the 2023-02-17 Lien post below as the create + diagnose sides of the same MySQL 8.0 primitive.

  • sources/2026-04-21-planetscale-what-are-the-disadvantages-of-database-indexes — JD Lien (PlanetScale, 2023-02-17) canonicalises the invisible-index mechanism as step 2 of the two-step safe-drop workflow: (1) detect unused-index candidates via the information_schema.STATISTICS cardinality = 0 heuristic; (2) flip candidate to INVISIBLE, measure, then DROP INDEX if safe (or VISIBLE to revert). Canonical framing verbatim: "With invisible indexes, you can keep the index intact but essentially hide the index from MySQL so that queries do not use the index. This gives you a way to quickly test the impact of removing an index without completely destroying it." Also discloses the PlanetScale deploy-request wrinkle (direct DDL is blocked on production branches — invisibility flip goes through deploy requests) and composes with schema revert as a two-layer safety net.

Last updated · 470 distilled / 1,213 read