Invisible Indexes in MySQL 8.0 and How They Are Used for Testing
Invisible indexes were introduced in MySQL 8.0 to allow developers and DBAs to test the effect of index removal without actually dropping the index. An invisible index exists on the table, is maintained during writes, but the optimizer completely ignores it during query planning.
A normal index that is hidden from the query optimizer.
MySQL will not use it for SELECT, UPDATE, DELETE, or JOIN plans.
The index is still updated during INSERT, UPDATE, DELETE operations.
It can be made visible again instantly without rebuilding.
To test whether an index is still used by queries.
To verify if an index can be safely removed.
To measure performance impact when disabling an index without dropping it.
To confirm whether query execution plans rely on that index.
If performance remains stable after making an index invisible, it is usually safe to drop it.
If queries slow down or break, revert quickly:
ALTER INDEX idx_email VISIBLE;
Optimizer completely ignores invisible indexes.
Exceptions: Using the
USE INDEX,FORCE INDEX, orIGNORE INDEXhints.Invisible indexes can be forced manually:
SELECT * FROM users FORCE INDEX(idx_email) WHERE email='a@b.com';
Safe index cleanup (large legacy schemas).
Testing performance regressions before index removal.
Debugging slow queries.
Avoiding downtime caused by index rebuilds.
Invisible indexes provide a powerful way to evaluate index usage and safely remove unnecessary indexes while minimizing risk in production environments.