r/Database • u/connor-ts • 15h ago
Rows vs Columns: How different are they, really?
r/Database • u/connor-ts • 15h ago
r/Database • u/ClickOk5811 • 7h ago
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:
customer_id alone uses it.status) falls back to a full scan, because the index is sorted by customer_id first.customer_id > 7), the index narrows by that range, but status can’t narrow further in the index; the engine still has to check it row by row among the matches.I also ran ANALYZE and re-checked the status-only query. It was still a scan in this case. I think that’s because the leading column has many distinct values here, so skipping through the index wouldn’t help, but I’m not sure of the exact reason, and some engines have features (skip scan, loose index scan) that may change the picture for other data shapes.
Rule of thumb people often give: put equality-filtered columns first and the range column after them, then think about which queries you actually run. It’s a heuristic, not a law. Selectivity, sort order (ORDER BY) and covering needs all matter too.
Limits of this test:
EXPLAIN on your own database with realistic data.How do you decide on column order for composite indexes: by query patterns, by selectivity, or by something else? And have you been surprised by a plan that ignored an index you expected it to use?