Column order matters

A regular customer checks their orders daily. The team builds one index on customer, then date, for their orders this month.

This chapter: Which requests can a two-column index serve?

About this chapter

You will learn

A composite index sorts by its first column first
It serves requests on that leading column
It can’t serve the second column alone

Words you’ll meet

Composite index: An index on more than one column, sorted by the first, then the next within it.
Leading column: A composite index’s first column, the one its entries are sorted by first.

Step 1 of 4

A sorts by customer, then by date within each customer, like surname then first name.

Ask for customer 57’s orders this month.

The SQL

CREATE INDEX ON orders (customer, day);

SELECT * FROM orders
WHERE customer = 57
  AND day BETWEEN DATE '2026-01-01' + 273 AND DATE '2026-01-01' + 296;

SELECT * FROM orders
WHERE day BETWEEN DATE '2026-01-01' + 273 AND DATE '2026-01-01' + 296;