Beginner LevelSQL & MySQL
How do B-Tree Indexes work in SQL/MySQL and how do you optimize slow queries using EXPLAIN?
❓ Interview Prompt: Explain B-Tree index structure, composite index column ordering rule (Leftmost Prefix Rule), and EXPLAIN ANALYZE output.
A B-Tree index in MySQL InnoDB stores data in a balanced tree structure where lookups, insertions, and deletions take $O(\log N)$ time.
Leftmost Prefix Rule:
A composite index on '(A, B, C)' can satisfy queries filtering on '(A)', '(A, B)', or '(A, B, C)', but NOT '(B)' or '(C)' alone.
EXPLAIN ANALYZE:
Running EXPLAIN ANALYZE reveals execution plans, scan types (type: ref, range, or ALL full table scan), and total rows examined.
Code Implementation Example
sql
-- Creating composite index following query access patterns
CREATE INDEX idx_users_status_created ON users(status, created_at);
-- Query using leftmost prefix (status = 'ACTIVE' and created_at range)
EXPLAIN ANALYZE
SELECT id, email, created_at
FROM users
WHERE status = 'ACTIVE'
AND created_at >= '2026-01-01'
ORDER BY created_at DESC;💡SQL composite indexing and EXPLAIN ANALYZE inspection.
🎯 Interview Key Takeaway
Always design composite indexes based on equality filter columns first, followed by range filters and sorting columns.
#SQL#MySQL#Indexes#Performance#EXPLAIN#Beginner
Back to all SQL & MySQL Questions→