A composite index is sorted by the first column, then by the second within each first-column value, and so on.
Leftmost prefix: INDEX(a, b, c) helps queries filtering on a, a+b, or a+b+c. It doesn’t help with b or c alone.
Ranges stop seeking: with WHERE a = 1 AND b > 10 AND c = 5, columns after the range (b) can’t be used to seek. Put equality columns first and the range column last, so (a, c, b) is better here.
Sorting: WHERE status = ? ORDER BY created_at avoids a filesort with INDEX(status, created_at), but not with the reverse order.
Covering: if every column the query needs is in the index, MySQL skips the table lookup entirely.
The leftmost-prefix rule
An index on (a, b, c) can serve queries that filter on:
a
a, b
a, b, c
It can’t seek efficiently on b alone, c alone, or b
Rule of thumb:
Equality columns first, starting with the one most queries use.
Then one range or ORDER BY column.
Optionally add extra columns to make it covering.
Example: INDEX(seller_id, status, created_at) serves seller_id, seller_id + status, and seller_id + status + date range.
Check with EXPLAIN: key_len shows how many columns were used, and Extra shows “Using filesort” or “Using index”.