Menu

Composite index column order: four queries, four query plans (tested on SQLite, check your own engine)

Reading 0:00
15s threshold

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) .…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.