Why Column Order Makes or Breaks a Composite Index
InnoDB stores secondary indexes as sorted B-Tree structures ordered from left to right. A composite index on (a, b, c) can satisfy queries filtering on (a) or (a, b), but cannot be used for an index seek on (b, c) alone. Even more importantly, MySQL stops using subsequent index columns for sorting or seeking the moment it encounters a range predicate (>, <, BETWEEN, or IN with multiple values).
The Equality → Sort → Range Rule
- Equality predicates first (
=): Place columns matched with exact equality first—ideally ordering higher-cardinality columns (likeworkspace_id) before low-cardinality flags (likestatus). - Sort columns second (
ORDER BY): Because all equality predicates narrow the B-Tree slice to contiguous leaf pages, MySQL can read rows already pre-sorted by the next column without running an expensiveUsing filesort. - Range predicates last (
>,BETWEEN): Place cursor pagination pointers (id > ?) or date boundaries last so they do not break index-assisted sorting.
Frequently Asked Questions
Why should cursor pagination use (status, id) instead of (id, status)?
In WHERE status = 'active' AND id > 500000 ORDER BY id ASC LIMIT 1000, status is an equality filter and id is both the sort and range column. Indexing (status, id) lets InnoDB jump straight to the exact leaf node and stream 1,000 sequential rows with zero filesort.
Do I still need a separate single-column index on the leftmost column?
No. Because (workspace_id, status, created_at) begins with workspace_id, MySQL already uses it for queries filtering only on workspace_id. Dropping redundant prefix indexes saves RAM in the InnoDB Buffer Pool and speeds up INSERT/UPDATE operations.