Composite Index
A composite index indexes multiple columns together; column order determines which query patterns it can serve (leftmost prefix principle).
Purpose
To understand how column order in multi-column indexes affects queries and design efficient composite indexes.
Mechanism
The index (a,b,c) serves WHERE a=?, WHERE a=? AND b=?, and WHERE a=? AND b=? AND c=?, but not WHERE b=? (violates the leftmost prefix). Put the most selective columns first.
Use cases
- Multi-column query optimization
- Index design
- Leftmost prefix