An index entry is a pair with four dials

Which four independent choices define every index entry, and what single tension sits under all of them?

CaseConstructed case · Priya at Parcelo

A partial index that works only in the console

Three weeks later, four things go wrong on the same day.

A dashboard query WHERE status = $1 against the pickups table (a work queue with 100 million rows, of which about 1,000 are pending) is slow. A partial index WHERE status = 'pending' exists and works in the SQL console, but not from the application.
A "covering" index she built last month, which made a report fast, has quietly become slow again on the busiest table. There is no error.
In the MySQL-based billing service, inserts into a table with UUID primary keys are slow and the table is huge on disk.
A query with a = 1 AND b > 5 AND c = 3 uses her index on (a, b, c) but still reads a large range.

All four come from the same small set of design choices.

Every index entry is a pair: a key and a reference. Four independent choices define the pair, and every item in this article is a position on one of four dials.

What is the key? A stored column, a tuple of columns, or a computed function of the row.
What does the reference point at? A physical address, a logical identity, the row itself, or nothing at all.
Which rows get an entry? All of them, a filtered subset, or every Nth.
What else rides in the entry? Nothing, or extra payload columns.

Dial	Choices covered here
1. Key	composite, expression
2. Reference target	primary/secondary, clustered/non-clustered
3. Rows that get entries	partial, sparse (and zone maps)
4. Payload	covering, INCLUDE

They are orthogonal. A partial, covering, composite expression index is a coherent thing, not a contradiction, and several of the best real indexes are exactly that.

The tension under all four is a single tension. An index entry is a copy of information. The more it copies, the more queries it can answer without touching the table, and the more writes it must intercept to stay true.