How does a composite index work, and what is the leftmost-prefix rule?

Summary

A composite index on (a, b, c) is sorted by a, then b, then c. It can serve queries that filter on a, on a and b, or on all three, but not on b or c alone. A range condition on a column stops later columns from being used to narrow the search.

Longer Answer

Think of a phone book sorted by last name, then first name. You can find "Smith", or "Smith, John", but not every "John".

Design rules:

• Put equality columns first, then the range or sort column: for WHERE customer_id = ? AND created_at > ? ORDER BY created_at, index (customer_id, created_at).
• An index can also satisfy ORDER BY (avoiding filesort) if the order matches the index after the equality columns.
• Column order by selectivity alone is a myth; order by how queries use the columns.
• One good composite index often beats several single-column indexes (MySQL can merge indexes, but it is usually slower).

Code Example

CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at);

-- Uses the index for both filtering and ordering
SELECT id, total FROM orders
WHERE customer_id = 42 AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

Key Points

• Sorted by columns left to right.
• Leftmost prefix must be used.
• Equality columns first, then range/sort.
• Ranges stop use of later columns for seeking.