MySQL Invisible Indexes: A Safe Path to Dropping Unused Secondary Indexes

The one-way door problem

Removing a secondary index in MySQL feels irreversible. You suspect an index is redundant—perhaps a composite index already covers its columns—but dropping it means betting that no ad-hoc query, report, or ORM-generated statement relies on it. If you are wrong, the table slows down and you must rebuild the index during peak traffic. MySQL 8.0 invisible indexes give you a middle path: hide the index from the query optimizer (the component that chooses which index to use), watch production behave, and restore it instantly if something breaks.

What invisible indexes actually do

An invisible index is still a real B-tree structure (the on-disk format MySQL uses for indexes). It continues to consume disk space and is maintained on every INSERT, UPDATE, and DELETE. The only thing that changes is optimizer visibility: by default, the optimizer will not consider the index when building an execution plan. This makes the feature a safe evaluation tool, not a write-performance optimization.

Prerequisites

• MySQL 8.0 or later (the INVISIBLE keyword was introduced in 8.0).
• The ALTER privilege on the target table.
• Access to EXPLAIN, Performance Schema, or the sys schema to capture baselines.
• A low-traffic window, because ALTER TABLE is a metadata operation that can wait behind long-running transactions.
• A backup or reversible migration path before any permanent DROP INDEX.

Step 1: Capture a baseline before touching anything

You cannot prove an index is unused if you never recorded how the workload behaved with it. Run EXPLAIN FORMAT=JSON against the heaviest queries that touch the table and save the output. Pay attention to the possible_keys and key fields. Then check whether the index already shows up as unused:

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema = 'your_db'
  AND object_name = 'orders';

Run this in a MySQL client connected to the primary instance. The sys views read from Performance Schema, so no special privilege beyond SELECT on those schemas is required. If the index appears here, it is a strong candidate, but a single view is not proof—some workloads only exercise certain indexes during month-end or batch jobs.

Step 2: Hide the index from the optimizer

Run the following on the primary instance. You need the ALTER privilege:

ALTER TABLE orders ALTER INDEX idx_customer_date INVISIBLE;

Replace orders and idx_customer_date with your table and index names. This is a metadata-only change, but it can still block behind a long transaction. Set a sane lock_wait_timeout in your session before running it so you fail fast rather than queueing behind an open transaction:

SET SESSION lock_wait_timeout = 10;
ALTER TABLE orders ALTER INDEX idx_customer_date INVISIBLE;

Expected check: the statement returns quickly. If it hangs, inspect performance_schema.metadata_locks or sys.schema_table_lock_waits to find the blocking transaction. Because this is DDL, it replicates to replicas under normal row-based or statement-based replication. The index remains invisible on replicas and is still maintained there. Session-level optimizer settings do not replicate.

Step 3: Observe the workload

Leave the index invisible for at least one full business cycle—daily batch windows, reporting jobs, and any scheduled tasks. Watch for:

• Application latency and error rates staying within tolerance.
• No new full table scans or filesorts (extra sorting passes that bypass indexes) in Performance Schema.
• Stable replication lag on replicas.

If you want to compare plans diagnostically without making the index visible to everyone, you can temporarily let your own session consider invisible indexes:

SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN FORMAT=JSON SELECT ... ;
SET SESSION optimizer_switch = 'use_invisible_indexes=off';

Run these in the same MySQL client session. The default for use_invisible_indexes is off; resetting it ensures normal sessions keep ignoring the hidden index. Compare the EXPLAIN output against your baseline to see whether the optimizer would have chosen the hidden index and what it chooses instead.

Step 4: Restore or drop

If latency regresses, error rates climb, or EXPLAIN shows the optimizer falling back to full scans, restore visibility immediately:

ALTER TABLE orders ALTER INDEX idx_customer_date VISIBLE;

This is another metadata operation and is effectively instant once it acquires the lock. No index rebuild is required.

If the index survives a full business cycle with no regressions, corroborate that it is genuinely unused, then drop it. Only do this after a backup or reversible migration is in place:

ALTER TABLE orders DROP INDEX idx_customer_date;

Unlike the visibility toggle, DROP INDEX removes the B-tree and frees storage. On a large table this can take time and hold metadata locks, so schedule it in a maintenance window.

How to confirm the current state

SHOW CREATE TABLE does not flag an index as invisible. Use one of these instead:

SHOW INDEX FROM orders;

Look at the Visible column; it reads YES or NO. Alternatively:

SELECT index_name, is_visible
FROM information_schema.statistics
WHERE table_schema = 'your_db'
  AND table_name = 'orders';

An IS_VISIBLE value of 0 means the index is hidden from the optimizer.

Limitations to keep in mind

• Write overhead remains. Invisible indexes are still updated on every write. If your goal is faster INSERT throughput, hiding an index will not help; only dropping it will.
• Storage is not reclaimed until you run DROP INDEX.
• Metadata locks matter. Even a fast metadata change waits for concurrent transactions. A long-running analytical query can delay the ALTER.
• Replication applies the DDL. The hidden state propagates to replicas, but session-level optimizer_switch changes do not.

Practical verification checklist

1. Baseline EXPLAIN FORMAT=JSON saved for representative queries.
2. SHOW INDEX or information_schema.statistics confirms Visible = NO.
3. Application latency and error rates remain within tolerance for a full business cycle.
4. sys.schema_unused_indexes or Performance Schema index statistics corroborate non-use.
5. Normal sessions have optimizer_switch set to the default (use_invisible_indexes=off).

If all five hold, the index is a safe candidate for DROP INDEX. If any fails, run ALTER TABLE ... ALTER INDEX ... VISIBLE and investigate the specific regression before trying again.