MySQL Invisible Indexes: A Reversible Safety Net Before Dropping Indexes

The irreversible drop

You suspect an index is dead weight. Maybe the application query changed, maybe the table grew and the optimizer stopped choosing it. The obvious move is DROP INDEX, but that is a one-way door. Rebuilding the index later means another ALTER TABLE, another lock window, and another chance to miss a workload that still needed it.

MySQL 8.0 offers a middle path: make the index invisible. The optimizer stops considering it, but InnoDB keeps maintaining it on every write. If nothing breaks, you drop it with confidence. If something does break, you flip it back to visible in seconds.

What "invisible" actually means

An invisible index is hidden from the query optimizer. When you run EXPLAIN, MySQL will not choose that index for a plan. However, the index is not removed. InnoDB, MySQL's default storage engine, still updates it on INSERT, UPDATE, and DELETE, so data integrity and uniqueness constraints remain enforced.

The syntax is straightforward:

ALTER TABLE orders ALTER INDEX idx_customer_id INVISIBLE;

To restore it:

ALTER TABLE orders ALTER INDEX idx_customer_id VISIBLE;

These are generally lightweight metadata operations in InnoDB. They do not rebuild the table, though you should still run them during a low-traffic window or use an online DDL tool if your environment is sensitive to brief metadata locks.

A reversible trial run

Here is a practical workflow on a MySQL 8.0 instance. Run these statements from a MySQL client with ALTER privileges on the target schema.

First, confirm your version. Invisible indexes do not exist in MySQL 5.7:

SELECT VERSION();

Next, pick a candidate index and capture the current plan. On a table named orders with an index idx_customer_id:

EXPLAIN SELECT * FROM orders WHERE customer_id = 1001\G

Note whether idx_customer_id appears in the key column. Then hide it:

ALTER TABLE orders ALTER INDEX idx_customer_id INVISIBLE;

Re-run the same EXPLAIN. The plan should change—perhaps a full table scan or a different index. If the plan is identical, the index was already unused by the optimizer, and hiding it proves nothing new.

After the application has run for a representative period, check whether the index is still being read. MySQL's built-in instrumentation database, performance_schema, includes a view called table_io_waits_summary_by_index_usage that aggregates I/O by index. A count of zero reads for an invisible index suggests the optimizer truly ignored it:

SELECT 
  object_name, 
  index_name, 
  count_read
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
  AND object_name = 'orders'
  AND index_name = 'idx_customer_id';

If count_read stays at zero across a full business cycle, you have evidence the index is unnecessary. If it is non-zero, something is still reading it—possibly a forced index hint or a query that cannot be rewritten—and you should keep the index.

Trade-offs worth knowing

Invisible indexes are a safety net, not a performance optimization. Because InnoDB still maintains the index, you do not reclaim disk space and you do not reduce write overhead—the extra work required to keep secondary structures synchronized with table data. Insert, update, and delete costs remain exactly as they were. The only benefit is the ability to observe optimizer behavior without committing to a destructive change.

There are compatibility risks. Some third-party tools, ORMs, and older clients may not display invisible indexes correctly, or may attempt to use them in ways that assume visibility. If your deployment pipeline includes schema diff tools, verify they understand the VISIBLE/INVISIBLE attribute before you rely on this pattern in production.

Finally, if the index was actually load-bearing, making it invisible can cause sudden query slowdowns. That is the point of the trial—but it means you should test in a staging environment first, and have a rollback plan ready.

When to make the call

Treat invisible indexes as a quarantine period. Hide the index, monitor EXPLAIN output and performance_schema for at least one full workload cycle, and confirm that query latency and I/O remain acceptable. If everything looks clean, execute DROP INDEX during a maintenance window. If not, run:

ALTER TABLE orders ALTER INDEX idx_customer_id VISIBLE;

and investigate why the optimizer still wanted it. The goal is not to avoid dropping indexes forever; it is to avoid dropping the wrong one.