Understanding Composite Indexes and the Importance of Column Order in MySQL
A composite index is an index created on two or more columns together. MySQL can use it to speed up queries that filter, sort, or join using the indexed columns.
Created on multiple columns (e.g.,
(col1, col2, col3)).More efficient than separate single-column indexes in many cases.
MySQL can use the index for lookups, sorting (ORDER BY), and grouping (GROUP BY).
Follows the leftmost prefix rule, meaning order matters.
The order of columns in a composite index is crucial because MySQL can only use the index starting from the leftmost columns.
For an index on
(A, B, C):MySQL CAN use: A — (A, B) — (A, B, C)
MySQL CANNOT use: B alone, C alone, or (B, C) without A
MySQL can partially use the index if the query includes the leftmost parts.
Composite index:
CREATE INDEX idx_user_name_age ON users(last_name, first_name, age);MySQL can use the index for:
✔
WHERE last_name = 'Doe'✔
WHERE last_name = 'Doe' AND first_name = 'John'✔
WHERE last_name = 'Doe' AND first_name = 'John' AND age = 30MySQL CANNOT use it for:
✘
WHERE first_name = 'John'✘
WHERE age = 30✘
WHERE first_name='John' AND age=30(no leftmost column)
In summary, a composite index is powerful, but only when the leftmost column(s) appear in the query. Choosing the correct column order is essential for optimal performance.