# Composite and covering indexes

> Column order decides which queries an index can serve; including columns lets it skip the table.

Concept · Transactions & indexes · https://levelcode.org/concepts/composite-indexes

A **composite index** covers several columns, such as `(customer_id, created_at)`. A B-tree sorts its entries by the first column, then by the second within equal first values, and so on, like a phone book sorted by last name and then first name.

## The leftmost-prefix rule

Because of that sort order, an index on `(a, b, c)` can serve:

| Query filters on | Uses the index? |
|---|---|
| `a` | Yes |
| `a, b` | Yes |
| `a, b, c` | Yes |
| `a` and a range on `b` | Yes; `c` can't narrow the range scan further |
| `b` or `c` alone | Not efficiently: the rows aren't grouped by `b` |

A phone book can't find "everyone named Alice" without reading every page. (PostgreSQL 18 and MySQL 8 can *skip scan* such an index when `a` has only a few distinct values, effectively running one lookup per value of `a`. Treat that as a bonus, not a design.)

**Order the columns** so that equality conditions come first and the range or sort column comes last:

```sql
CREATE INDEX orders_customer_recent ON orders (customer_id, created_at DESC);

-- Served entirely by the index: jump to customer 42, read the first 20 entries in order
SELECT id, total FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
```

The same index serves [keyset pagination](https://levelcode.org/concepts/keyset-pagination) for that customer, since `WHERE customer_id = 42 AND created_at < $cursor` is still equality followed by a range.

## Covering indexes

Normally the index finds row locations and the database then fetches each row from the table, which is called the *heap*. If the index already contains every column the query needs, it can skip the table entirely. That's an **index-only scan**.

```sql
CREATE INDEX orders_customer_recent ON orders (customer_id, created_at DESC) INCLUDE (total);
```

`INCLUDE` adds columns to the index leaves without making them part of the sort key. In PostgreSQL, an index-only scan still has to check the visibility map, so tables that are frequently updated and not recently vacuumed get less benefit.

## Costs and habits

- **Every index slows writes** and takes disk and cache space. One well-ordered composite index often replaces two or three single-column ones.
- **An index on `(a, b)` makes an index on `(a)` redundant.** Drop the shorter one.
- **Check with `EXPLAIN`.** The plan will show `Index Scan`, `Index Only Scan`, or a `Seq Scan` telling you the index didn't help. The [indexes lesson](https://levelcode.org/roadmaps/sql/indexes) covers when not to index at all, and [B-trees vs LSM-trees](https://levelcode.org/concepts/b-tree-vs-lsm-tree) covers what's underneath.