Intermediate LevelSQL & MySQL
What is the Equality First, Range Second rule for Composite Index column ordering?
❓ Interview Prompt: Explain Composite Index column ordering rules: Equality columns first, Range filter columns second, and ORDER BY columns third.
Designing high-performance composite indexes follows the Equality-First, Range-Second rule:
Ordering Rule:
1
Equality Columns First: Place columns filtered with '=' or 'IN (...)' first in the index.
2
Range Columns Second: Place columns filtered with '>', '<', 'BETWEEN', or 'LIKE 'term%'' second.
3
Sorting Columns Third: Place 'ORDER BY' columns third.
•
Reason: Once a B-Tree index scan encounters a range filter column, subsequent index columns CANNOT be used for equality filtering!
Code Implementation Example
sql
-- Query: WHERE tenant_id = 5 AND status = 'ACTIVE' AND created_at >= '2026-01-01'
-- Optimal Composite Index: (tenant_id, status, created_at)
CREATE INDEX idx_orders_tenant_status_date
ON orders(tenant_id, status, created_at);💡Designing an optimal composite index following the Equality-First, Range-Second rule.
🎯 Interview Key Takeaway
Order composite index columns with equality filters first, range filters second, and sorting columns third.
#SQL#Composite Index#Index Ordering#Performance#Intermediate
Back to all SQL & MySQL Questions→