Part 2 of 5 · 1 min read
A composite index orders several columns together
Columns are concatenated and sorted as one combined key, in a fixed order.
Think of a phone book sorted first by last name, and only within each last name, by first name. "Smith, Anna" and "Smith, Ben" sit next to each other, sorted by first name only inside the Smith group.
In plain words: a composite index, also called a concatenated index, builds its sorted key from more than one column, concatenated together in a fixed, declared order. The index is sorted first by the first column, then, within each group of matching first-column values, by the second column, and so on. This single structure can serve a query filtering on just the first column, or on the first column together with the second, but it generally cannot efficiently serve a query filtering only on the second column alone.
A worked example. An index on (customer_id, placed_on) is sorted first by customer_id, and within each customer's group of rows, by placed_on. It serves WHERE customer_id = 42 well, and WHERE customer_id = 42 AND placed_on = '2026-01-01' even better, but gives little help to WHERE placed_on = '2026-01-01' alone.
The key lines.
CREATE INDEX idx_orders_cust_date ON orders (customer_id, placed_on);
SELECT * FROM orders WHERE customer_id = 42 AND placed_on >= '2026-01-01';
-- uses the index fully: filters on both columns, in the index's declared order
Composite index
A composite index, or concatenated index, sorts its entries by more than one column, concatenated together in a fixed, declared column order.
Example: a phone book sorted by last name, then by first name within each last name, is a composite index over those two columns.