Senior LevelSQL & MySQL
How do Invisible Indexes enable safe, zero-downtime index removal in MySQL 8.0?
❓ Interview Prompt: Explain MySQL 8.0 Invisible Indexes (`ALTER TABLE ... ALTER INDEX ... INVISIBLE`), query optimizer testing, and safe index deletion.
Deleting an index in production is dangerous: if an unexpected query relied on it, query performance will collapse! MySQL 8.0 introduced Invisible Indexes to mitigate this risk:
Workflow:
1
Mark index as INVISIBLE: 'ALTER TABLE users ALTER INDEX idx_email INVISIBLE;'.
2
The query optimizer will ignore the index when building execution plans, while InnoDB continues updating the index on writes.
3
Monitor query performance. If no performance regressions occur $\rightarrow$ Safely drop the index!
Code Implementation Example
sql
-- Toggling index visibility for safe optimizer evaluation
ALTER TABLE orders ALTER INDEX idx_created_at INVISIBLE;
-- If performance degrades, instantly restore index visibility without rebuilding!
ALTER TABLE orders ALTER INDEX idx_created_at VISIBLE;💡Toggling index visibility for safe production performance testing.
🎯 Interview Key Takeaway
Use Invisible Indexes in MySQL 8 to test the impact of index removals safely before dropping them.
#MySQL 8#Invisible Indexes#Performance#Indexing#Senior
Back to all SQL & MySQL Questions→