A composite index with the wrong column order forces an inefficient range scan instead of a targeted seek.
Codesql
-- Goal: quickly fetch a customer's recent orders
CREATE INDEX idx_orders_lookup
ON orders (created_at, customer_id);
EXPLAIN
SELECT order_id, total
FROM orders
WHERE customer_id = 42
AND created_at >= '2024-01-01'
ORDER BY created_at DESC;
-- EXPLAIN shows: type=range, rows=850000, Extra=Using index condition
-- (scans every order since 2024, filtering customer_id afterwards)
Why does this query scan far more rows than expected, and how should the index be fixed?