A composite index on (customer_id, status) is not the same as one on (status, customer_id) . Instead of explaining it abstractly, here’s a small test. I used SQLite 3.53.1 because it’s easy to reproduce, with a table of 20,000 rows, about 500 distinct customers and 3 statuses:
CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INT, status TEXT, created_at TEXT ); CREATE INDEX idx_cust_status ON orders (customer_id, status);
Then EXPLAIN QUERY PLAN for four queries:
WHERE customer_id = 7 -> SEARCH orders USING INDEX idx_cust_status (customer_id=?) WHERE customer_id = 7 AND status = 'paid' -> SEARCH orders USING INDEX idx_cust_status (customer_id=? AND status=?) WHERE status = 'paid' -> SCAN orders WHERE customer_id > 7 AND status = 'paid' -> SEARCH orders USING INDEX idx_cust_status (customer_id>?)
What it shows:
The index helps when the query constrains the leading column(s) .…