How does the InnoDB B+Tree Index work internally, and why does Leftmost Prefix Rule dictate composite index column ordering?

Why B+Tree Needs Leftmost Prefix Rule

In an InnoDB B+Tree, index keys are sorted lexicographically based on the order of columns defined in the index: (ColA, ColB, ColC).

• If a query filters on ColB without ColA, the B+Tree root cannot branch because ColB is only sorted within duplicate values of ColA.
• Range Queries Stop Index Filtering: Once a range operator (>, <, BETWEEN, >=) is evaluated, subsequent columns in the composite index cannot be used for B+Tree traversal.

Fixing the Slow Query

Because created_at used a range operator (>=), placing it first broke index lookups for status and user_id.

Optimal Composite Index Column Order:

1. Exact equality matches with highest cardinality first: user_id
2. Equality match: status
3. Range match last: created_at

ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

Query execution time drops from 8.5 seconds (full scan) to 0.8 milliseconds (index seek).