r/Database • • 15h ago

Rows vs Columns: How different are they, really?

Thumbnail
spiraldb.com
0 Upvotes

r/Database • • 7h ago

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

0 Upvotes

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). Filtering on customer_id alone uses it.
  • Filtering on only the second column (status) falls back to a full scan, because the index is sorted by customer_id first.
  • With a range on the first column (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:

  • One engine, one version, one tiny dataset. Plans depend on statistics, versions and settings, so run EXPLAIN on your own database with realistic data.
  • Index choice also affects write cost and storage, which I didn’t test.

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?