Composite indexes and column order in SQL

A two-column index is one ordered list, so the leading column is the one you can search.

CREATE INDEX orders_by_customer_date
    ON orders (customer_id, placed_at DESC);

EXPLAIN QUERY PLAN
SELECT
    id,
    total
FROM orders
WHERE customer_id = 42
ORDER BY placed_at DESC
LIMIT 10;

How it works

1. (a, b) serves a filter on a, and on a plus b — but not on b alone.
2. Put the equality column first and the range or sort column second.
3. The index also satisfies the ORDER BY when it matches its own order.

Keywords and builtins used here

• BY
• CREATE
• DESC
• EXPLAIN
• FROM
• INDEX
• LIMIT
• ON
• ORDER
• SELECT
• WHERE

The run, in numbers

Lines

11

Characters to type

183

Tokens

32

Three-star pace

90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 21 seconds.

Type this snippet

Step 2 of 5 in Indexes, step 4 of 17 in Plans & performance.

← Previous Next →