Composite Index

ELI5 — The Vibe Check

A composite index indexes multiple columns together, like a phone book sorted by last name AND first name. The order matters a lot. An index on (country, city) is useless if you only search by city. Think of it like a filing cabinet: you can find all files in drawer A, but you can't jump to a specific folder without opening the drawer first.

Real Talk

A composite (multi-column) index indexes two or more columns as a single B-Tree. The leftmost prefix rule means the index supports queries that filter on the first column, first two columns, etc., but not queries that skip leading columns. Column order should match query patterns, with high-selectivity columns first.

Show Me The Code

-- Composite index: supports queries on (tenant_id), (tenant_id, status), and (tenant_id, status, created_at)
CREATE INDEX idx_orders_composite
ON orders(tenant_id, status, created_at);

-- Uses the index:
SELECT * FROM orders WHERE tenant_id = 1 AND status = 'active';

-- Does NOT use the index (skips leading column):
SELECT * FROM orders WHERE status = 'active';

When You'll Hear This

"Column order in composite indexes is everything." / "Our composite index on (user_id, created_at) covers both the filter and the sort."