Q27: What is a Composite Index and the Leftmost Prefix Rule?
A composite index spans multiple columns (e.g., INDEX (col1, col2, col3)).
The Leftmost Prefix Rule
The database query planner can use a composite index only if query predicate conditions match column combinations starting from the leftmost column onward.
WHERE col1 = x$\rightarrow$ Uses IndexWHERE col1 = x AND col2 = y$\rightarrow$ Uses IndexWHERE col2 = y AND col3 = z$\rightarrow$ Cannot Use Index (Missing leftmost prefixcol1)
Key Takeaways
- Composite indexes evaluate conditions following the leftmost prefix order.
- Place high-cardinality, frequently filtered columns at the beginning of composite index definitions.