SQL composite index column order: filter before sort
A composite index contains more than one column, ordered first by the leftmost column and then by the next. In the example, customer_id narrows the search to one customer and created_at orders that customer's rows by date. Reversing the columns can preserve the order while requiring a scan across other customers.
By QueryPlan. Published . Updated .
On this page
Start with the source rows
The table has three orders for customer 42 and one for customer 7. We want customer 42's orders, newest first. Both queries return orders 2, 1, and 4 with the same columns and values. A customer with no orders, such as customer 999, produces no rows with either index.
Swipe or scroll the table to see all columns.
| id | customer_id | created_at | total_cents |
|---|---|---|---|
| 1 | 42 | 2026-01-03 | 1900 |
| 2 | 42 | 2026-01-05 | 2200 |
| 3 | 7 | 2026-01-04 | 1800 |
| 4 | 42 | 2026-01-01 | 900 |
Schema and sample SQL
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
created_at TEXT NOT NULL,
total_cents INTEGER NOT NULL
);
CREATE INDEX idx_orders_created_customer ON orders(created_at DESC, customer_id);INSERT INTO orders (id, customer_id, created_at, total_cents) VALUES
(1, 42, '2026-01-03', 1900),
(2, 42, '2026-01-05', 2200),
(3, 7, '2026-01-04', 1800),
(4, 42, '2026-01-01', 900);Before: date first
With created_at first, index entries follow date order across all customers. SQLite can return matching rows in the requested order without a temporary sort, but the entries for customer 42 are mixed with other customers. The plan shows SCAN because SQLite scans the index and checks which entries match. The query still returns the correct result.
SELECT id, created_at, total_cents
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;Swipe or scroll the table to see all columns.
| id | created_at | total_cents |
|---|---|---|
| 2 | 2026-01-05 | 2200 |
| 1 | 2026-01-03 | 1900 |
| 4 | 2026-01-01 | 900 |
- SCAN
orders USING INDEX idx_Scans entries in the index ordered by date first.orders_ created_ customer
After: customer first
With customer_id first, matching index entries are together. Within each customer, created_at descending puts the newest orders first. SQLite reports SEARCH with no temporary sort. The setup removes the first index so each query runs with only the index being compared.
DROP INDEX idx_orders_created_customer;
CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);SELECT id, created_at, total_cents
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;Swipe or scroll the table to see all columns.
| id | created_at | total_cents |
|---|---|---|
| 2 | 2026-01-05 | 2200 |
| 1 | 2026-01-03 | 1900 |
| 4 | 2026-01-01 | 900 |
- SEARCH
orders USING INDEX idx_Finds the matching rows through an index.orders_ customer_ created (customer_ id=?)
What the comparison tells you
Neither index includes total_cents, so SQLite still reads that value from each matching table row. The example compares column order, and neither index covers the query. The plan shows the access method, but it does not report elapsed time or the exact number of entries read.
The displayed plans were checked with the SQLite engine used in the Visualizer. Different data, queries, statistics, or SQLite versions can change the plan. PostgreSQL and MySQL can choose and report plans differently. Before adding an index, check the result and plan in your database. Then measure representative queries and the extra storage and write cost.
Check your understanding
Why can an index with customer_id first search for customer 42 without scanning entries for other customers?
Show the explanation
The first column groups entries by customer_id. The equality filter selects customer 42's entries, and the next column orders those entries by date. An index with the date first mixes entries for different customers.