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.

What Is an Invisible Index?

1
A normal index that is hidden from the query optimizer.
2
MySQL will not use it for SELECT, UPDATE, DELETE, or JOIN plans.
3
The index is still updated during INSERT, UPDATE, DELETE operations.
4
It can be made visible again instantly without rebuilding.

Creating an Invisible Index

Making an Existing Index Invisible

Why Use Invisible Indexes?

1
To test whether an index is still used by queries.
2
To verify if an index can be safely removed.
3
To measure performance impact when disabling an index without dropping it.
4
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.

Making an Invisible Index Visible Again

1
If queries slow down or break, revert quickly:
2
```
ALTER INDEX idx_email VISIBLE;
```

Optimizer Behavior

1
Optimizer completely ignores invisible indexes.
2
Exceptions: Using the USE INDEX, FORCE INDEX, or IGNORE INDEX hints.
3
Invisible indexes can be forced manually:

SELECT * FROM users FORCE INDEX(idx_email) WHERE email='a@b.com';

Use Cases in Production

1
Safe index cleanup (large legacy schemas).
2
Testing performance regressions before index removal.
3
Debugging slow queries.
4
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.