The leftmost prefix rule, in one example

A composite index on (a, b, c) serves queries filtering on a, on a and b, and on all three — and does nothing for a query filtering on b alone.

CREATE INDEX idx ON orders (status, customer_id, placed_at);

WHERE status = 'paid'                          -- uses it
WHERE status = 'paid' AND customer_id = 12     -- uses it
WHERE customer_id = 12                         -- does not
WHERE status = 'paid' AND placed_at > ?        -- uses status only
WHERE status IN ('paid','sent') AND customer_id = 12   -- uses both

The fourth line is the one that catches people: a range on the second column stops the index being used for anything after it, so column order should put equalities first and the range last. Reversing the order of two columns produces a genuinely different index, which is why a table with four overlapping composite indexes usually needs two. Reading EXPLAIN‘s key_len tells you how much of the index was actually used, which is the detail that settles the argument.