Senior LevelSQL & MySQL
What is Index Merge Optimization in MySQL and when should you replace it with a Composite Index?
❓ Interview Prompt: Explain Index Merge Optimization (`Index Merge Intersection`, `Index Merge Union`), multiple single-column index merging, and replacing with composite indexes.
When a query filters on multiple columns with separate single-column indexes, MySQL executes an Index Merge Optimization:
How Index Merge Works:
•
Scans two separate B-Tree indexes independently and merges their row pointers in memory using Intersection or Union.
•
⚠️ Suboptimal Performance: Index Merge is slower than a single tailored Composite Index on '(colA, colB)'. When EXPLAIN displays 'Using intersect(...)', replace single-column indexes with a composite index!
Code Implementation Example
sql
-- EXPLAIN displaying Index Merge warning:
-- Extra: Using intersect(idx_status, idx_created); type: index_merge
-- Optimal Solution: Replace with single Composite Index!
CREATE INDEX idx_status_created ON users(status, created_at);💡Replacing Index Merge execution paths with an optimal Composite Index.
🎯 Interview Key Takeaway
Replace Index Merge execution paths with a single tailored composite index for maximum query performance.
#MySQL#Index Merge#Composite Index#Performance#Senior
Back to all SQL & MySQL Questions→