The index starts with the query

Begin with one request, not one column

An operations page needs the next 50 open orders for one tenant. It sorts newest first and uses a cursor, because new orders can arrive while an operator pages through the list. Our illustrative query is:

SELECT id, created_at, total_cents
FROM orders
WHERE tenant_id = 42
  AND status = 'open'
  AND (created_at, id) < ('2026-09-22 10:30:00', 9182)
ORDER BY created_at DESC, id DESC
LIMIT 50;

The useful candidate is a composite B-tree key on (tenant_id, status, created_at DESC, id DESC). This example is ours; the method comes from two complementary lessons. Arpit Bhayani builds an index from keys and row locations to explain reduced page reads. Hello Interview’s data-modeling lesson starts from API access patterns instead of choosing a database structure in isolation.

The equality prefix finds a narrow neighborhood

The engine can seek to the part of the tree whose first two values are (42, 'open'). Within that neighborhood, entries are already ordered by creation time and the unique ID. The cursor supplies an exclusive upper bound, and the engine can walk entries in order until it finds 50 visible matching rows. It does not need to sort every open order for the tenant.

The ID matters when two rows share a timestamp. Without it, a cursor can skip or repeat rows at that boundary. This resolves ties; changes to a row’s status or ordering key can still change the paginated view. A stable snapshot requires a separate consistency contract. The index leaf ultimately identifies table rows; whether the engine can return every selected field directly from the index depends on its index type, visibility rules and included data.

The same columns in another order are another index

Put created_at first and the tree is primarily arranged by time across every tenant and status. Once the scan opens a time range, later key parts may help filter, but they no longer give this request one compact tenant-and-status interval. Put only tenant_id first and the database may still inspect a large set of that tenant’s paid, cancelled and open rows, then sort what remains.

Hello Interview’s indexing survey distinguishes ordered, spatial and text access paths. The shared principle is that the structure must preserve the dimension the query needs to narrow. A valid index is not automatically the useful index.

Selectivity decides whether the shortcut is shorter

If an export asks for every order, following millions of index entries and then fetching table rows can cost more than a sequential scan. Cached pages, tenant skew, stale statistics, row width and the fraction returned all affect the choice. The optimizer estimates those costs; the production-shaped plan and latency distribution tell us whether its estimate worked.

Test the exact predicate, ordering and limit with representative data. Look for the index actually chosen, the rows scanned versus returned, an avoidable sort, and repeated table lookups. Then test a large tenant and common status, not only a tidy development tenant. An index that wins on uniform fixtures can lose on the hot customer that matters.

Every extra read path joins the write path

Inserting an order now changes the table and this index. Changing its status from open to paid removes one logical entry and adds another. The database logs those changes, maintains tree pages and replicates the additional bytes according to its durability setup. Adding total_cents to cover this read can avoid a table lookup, but every total correction then rewrites index state too.

This is the cost side of the page-saving model in Arpit’s lesson, and it is why the query-first advice matters. Keep the smallest set of indexes that supports measured access paths and invariants. Record which endpoint owns each one. When a query disappears, confirm the index is unused before removing it. When a new filter arrives, do not append its column reflexively: write the intended scan from equality prefix through range and order, then decide whether that read deserves another write cost.