A composite index where column order changed execution by 40x

When filtering by an equality condition on one column and a range condition on another, the order of columns in a composite index is the difference between an index seek and an index scan.

-- Query:
SELECT * FROM notifications 
WHERE status = 'pending' AND created_at >= '2026-10-01' 
LIMIT 50;

-- BAD: Range column first stops composite index matching for subsequent columns:
INDEX (created_at, status) -- Scans all rows created since Oct 1 (800,000 rows, 480ms)

-- GOOD: Equality column first:
INDEX (status, created_at) -- Seeks directly to 'pending', scans only date range (12ms)

In a B-tree index, once a column is evaluated with a range comparison (>, <, BETWEEN), MySQL cannot use subsequent index columns for filtering. Placing all equality columns on the left of the composite definition is the fundamental rule.