MySQL Tuning, Indexing & Concurrency
8-10 Years (Tech Lead)
Medium
Production Scenario
How do you safely rename a column or drop an index in MySQL production without taking locks?
Real-World Scenario / Incident Description
Dropping an unused index on a busy 80M row table caused application write queries to stall.
277 views
•
Updated 1 month ago
MySQL 8 Invisible Indexes & Online Drop
- Make Index Invisible First:
Verifies if optimizer or queries degrade without that index.ALTER TABLE orders ALTER INDEX idx_old_status INVISIBLE; - Drop Online:
ALTER TABLE orders DROP INDEX idx_old_status, ALGORITHM=INPLACE, LOCK=NONE;
Related Concepts:
#Online DDL
#Index Management
#Production Safety
More MySQL Tuning, Indexing & Concurrency Interview Questions
How does the InnoDB B+Tree Index work internally, and why does Leftmost Prefix Rule dictate composite index column ordering?
Read →
Explain InnoDB Locking Modes: Record Locks, Gap Locks, and Next-Key Locks, and how they prevent Phantom Reads.
Read →
How do you optimize deep pagination (`SELECT * ... LIMIT 5000000, 20`) on large tables with 100M rows?
Read →
How do you interpret MySQL `EXPLAIN ANALYZE` output to diagnose `Using filesort` and `Using temporary`?
Read →
Explain MySQL Table Partitioning (Range, List, Hash) vs. Application-Level Sharding for 1TB+ databases.
Read →