MySQL 8's Invisible Indexes: Why It Saved My Day

As an SRE at a large-scale web company, database performance incidents are part of my daily life.

Recently, I encountered an issue where a developer decided to replace an index with a better performant one by marking old index invisible. However, other team had queries that previously relied on this index started running much slower, affecting a subset of our application.

Fortunately, because MySQL 8.0 introduced Invisible Indexes, we were able to quickly revert the change without the usual overhead of dropping and re-adding indexes. This feature turned what could have been a prolonged outage into a quick recovery.

---

What Are Invisible Indexes in MySQL 8.0?

Invisible Indexes were introduced in MySQL 8.0 as a way to make an index effectively "disappear" from query execution plans without physically dropping it. When an index is marked as INVISIBLE, the optimizer ignores it when planning query execution. However, the index is still maintained and updated in the background for any data modifications.

Key characteristics of Invisible Indexes:

• They remain fully maintained and up-to-date but are ignored for reads.
• Queries will no longer consider them for execution plans.
• They can be made VISIBLE again instantly without re-creating them.

Feature	Visible Indexes	Invisible Indexes
Used in Query Plans	Yes	No
Maintained on Data Changes	Yes	Yes
Can be Quickly Reverted	No (requires re-creation)	Yes (instant toggle)
Affects Table Schema	Yes (when dropped/added)	No (just toggled)

Why Invisible Indexes Provide Faster Rollbacks

Traditionally, testing the impact of an index involved dropping it entirely and observing performance changes. However, dropping an index is an expensive operation:

• It locks the table, causing write stalls.
• If the index is needed again, re-creating it is a time-consuming and resource-intensive operation.
• Query performance during re-creation might be degraded.

With Invisible Indexes, we can simply mark an index as INVISIBLE, observe performance changes, and, if necessary, revert it back to VISIBLE instantly. This provides a low-risk, quick rollback mechanism for index tuning.

---

Conclusion

Invisible Indexes in MySQL 8.0 provide a powerful tool for safe index experimentation and quick rollbacks. In our case, what could have been a prolonged outage was resolved instantly by toggling an index back to VISIBLE. As an SRE, knowing how to leverage this feature can mean the difference between extended downtime and a quick, seamless recovery.

Next time you’re considering dropping an index, consider making it INVISIBLE first—you might just save yourself from a database performance fire drill.