What are invisible indexes in MySQL 8.0, and how can they be used for testing?

Question

Accepted Answer

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. CREATE INDEX idx_email ON users(email) INVISIBLE; ALTER INDEX idx_email INVISIBLE; 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: ```sql
ALTER INDEX idx_email VISIBLE;
``` Optimizer *completely ignores* invisible indexes. Exceptions: Using the `USE INDEX`, `FORCE INDEX`, or `IGNORE INDEX` hints. Invisible indexes can be forced manually: ```sql
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.

What are invisible indexes in MySQL 8.0, and how can they be used for testing?

Invisible Indexes in MySQL 8.0 and How They Are Used for Testing

What are invisible indexes in MySQL 8.0, and how can they be used for testing?

Invisible Indexes in MySQL 8.0 and How They Are Used for Testing

Scenario Questions

Follow-up Questions