A query filters on tenant_id and status and sorts by created_at. How do you order the columns in a composite index, and why?
Key points
- A composite index is sorted by the first column, then the second within it, so it can only be used left to rightIn plain words: A multi-column index is like a phone book sorted by surname then first name, so it only helps if you start with the surname
- Put equality predicates first, then the range or sort column, so the index delivers rows already in the desired orderIn plain words: Put the columns you match exactly first and the one you sort or range over last, so rows come out already in order
- Here (tenant_id, status, created_at) lets the engine seek to one tenant and status and read in time order with no sort stepIn plain words: In this case the database jumps to one tenant and one status and reads straight down in time order with no sorting
- Putting the range column before an equality column forces a scan across many values and a separate sortIn plain words: Put the range column too early and the database must wade through many values and sort afterwards
- Column order also decides which other queries can reuse the index via its prefixIn plain words: The order also decides which other queries can use the same index, since they must start from its first columns
Model answer
Think of a composite index as a phone book sorted by last name, then first name. You can find all "Smiths" quickly and all "Smith, Johns" quickly, but you cannot find all "Johns" without reading the whole book. Column order determines which lookups are fast.
The query
SELECT id, title FROM tickets
WHERE tenant_id = ? AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;
The rule: equality columns first, then the range or sort column.
Index (tenant_id, status, created_at):
- Seek to the block of entries where
tenant_id = X. - Within it, seek to
status = 'open'. - Those entries are already ordered by
created_at, so read the last 50 backwards and stop. No sort, no extra rows.
Index (created_at, tenant_id, status): the engine cannot seek on tenant_id because entries are grouped by time first. It must scan the time range (potentially all of it), filtering rows as it goes.
Index (tenant_id, created_at, status): seeks to the tenant, reads in time order, but must check status on every entry and skip the closed ones, so a tenant with mostly closed tickets reads far more than 50 entries.
Second-order considerations
- Prefix reuse.
(tenant_id, status, created_at)also servesWHERE tenant_id = ?andWHERE tenant_id = ? AND status = ?. Order shared prefixes so the most common queries benefit. - Covering. Add
titleas an included column so the query is answered entirely from the index. - Low cardinality is fine in a composite index when it follows an equality on a selective column; it still narrows the block you read.
Say the rule, show the seek, and note which other queries get a free ride.
Likely follow-ups
- What if status has three values and queries often omit it?
- How would you include a selected column to make the index covering?