Composite Indexes Explained: Why Column Order Is the Whole Game

A composite index on (a, b, c) stores rows sorted by a first, then b within equal a groups, then c within equal (a, b) groups. Every behaviour follows from that sort order. If you skip the leftmost column, matching values aren't contiguous, so the database can't efficiently navigate the index.

The key strategy is equality columns before range columns. An equality predicate narrows to a contiguous group where the next column remains sorted. A range predicate breaks contiguity for everything after it. This same logic explains when an index can serve ORDER BY without a filesort — if your equality filter lands you in a group that's already sorted the way you need.

One well-ordered composite index almost always beats multiple single-column indexes for multi-column queries. But indexes cost writes, and if one index is a prefix of another, the shorter one is usually redundant. Audit, then drop.