✏️ Explanatory Question
Level: Advanced — Understanding the "leftmost prefix" rule is a strong signal of query-tuning skill.
A composite index (also called a multi-column or concatenated index) is a single index built on two or more columns. It is sorted first by the leftmost column, then by the next, and so on — like sorting a list by last name, then first name.
(a, b, c)?| WHERE Condition | Uses Index? | Reason |
|---|---|---|
a = ? |
Yes | Leftmost column |
a = ? AND b = ? |
Yes | Prefix a, b |
a = ? AND b = ? AND c = ? |
Yes (fully) | Full index |
b = ? |
No | Skips leftmost column a |
a = ? AND c = ? |
Partial (only a) | Gap at b breaks the prefix |
-- Composite index: order is (dept_id, salary)
CREATE INDEX idx_dept_sal ON employees(dept_id, salary);
-- USES index (leftmost prefix)
SELECT * FROM employees WHERE dept_id = 10;
SELECT * FROM employees WHERE dept_id = 10 AND salary > 50000;
-- Does NOT use the index efficiently (skips leftmost dept_id)
SELECT * FROM employees WHERE salary > 50000;
-- Verify with EXPLAIN
EXPLAIN SELECT * FROM employees WHERE dept_id = 10 AND salary > 50000;