MySQL B-Tree Indexing

Composite Index & Migration Builder

Enter your table name and query predicates to order B-Tree columns using the Equality → Sort → Range rule—eliminating Using filesort on large tables.

Deduplicates columns automatically • Outputs Laravel & Raw SQL

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

  1. Equality predicates first (=): Place columns matched with exact equality first—ideally ordering higher-cardinality columns (like workspace_id) before low-cardinality flags (like status).
  2. 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 expensive Using filesort.
  3. 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.