How do composite indexes work?

Direct answer

What a strong answer covers

How it works

A composite index sorts entries by the first column, then by the second within equal values of the first, and so on. The engine can use it for a predicate on the first column alone, on the first and second, and so forth, but not on the second column alone, because those values are scattered across the tree. Column order therefore follows the queries: equality predicates first, then the column used for range or ordering, and columns needed only in the SELECT list can be appended or included so the index covers the query without a table lookup. One well-ordered composite index often replaces several single-column ones.

Example

For SELECT * FROM orders WHERE customer_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20, an index on (customer_id, status, created_at DESC) lets the engine seek to the customer and status and read the first twenty rows already in order. An index on (created_at, customer_id) would scan every recent order across all customers; an index on (status) alone would match millions of rows.

Common mistakes

• Creating (b, a) when every query filters on a and only sometimes on b.
• Assuming an index on (a, b) serves a query that filters only on b.
• Adding a composite index per query and paying for ten indexes that overlap.

A useful answer structure

Answer from your own work.

Do not memorise a script: use only examples you can explain honestly in follow-up questions.