Database indexes

01Build another route to the same records

A support agent asks for the latest 50 open tickets belonging to customer organization 17. The database stores five million tickets across many organizations, also called tenants. Reading every ticket just to find those 50 would do unnecessary work.

An index gives the database another route to records:

1. Save selected field values in an extra structure, together with a way to reach the corresponding record or its needed values.
2. Search that smaller or better-organized structure for the requested group.
3. Read the matching entries and fetch any remaining fields from the table.
4. Maintain the extra structure when relevant records change.

An ordered structure called a B-tree is common. It organizes searchable key ranges into pages, so the database can locate a group and then walk entries in order rather than inspect every row.

Start with the actual screen, not the instruction “add an index.” This screen needs:

• Tickets for one tenant.
• Only open tickets within that tenant.
• The most recently updated tickets first.
• At most 50 results.

Word search, geographic search, and broad summaries can need different structures. One index is not a universal accelerator for every way of reading a table.

02Match filtering and ordering

Write the query before proposing a key:

SELECT id, updated_at, subject
FROM tickets
WHERE tenant_id = 17 AND status = 'open'
ORDER BY updated_at DESC
LIMIT 50;

Test a B-tree key on (tenant_id, status, updated_at). Its field order groups the data in a useful sequence:

1. Locate entries for tenant 17.
2. Within that tenant, locate the open-status group.
3. Walk that group in the required time order, using the engine’s supported scan direction.
4. Stop after enough visible matching records have been found, then fetch any fields not available from the index.

Putting time first, as in (updated_at, tenant_id, status), instead groups globally recent tickets. The database may examine many other tenants’ entries before finding this tenant’s 50 results. LIMIT 50 limits returned rows, not necessarily examined entries.

Consider two refinements only after the basic path is clear:

• A partial index stores only records satisfying a stated filter, such as open tickets. The query must imply that filter, and the database must support this index type.
• A covering index includes the fields needed for the screen. This can avoid separate table lookups, but some engines still inspect table information to check which row version the query may see. Including a large ticket body makes entries bigger and writes more expensive.

Do not turn key order into an absolute rule. Some engines can probe several leading-value groups, called a skip scan, or combine multiple indexes. Inspect the execution plan: the scans, lookups, and sorts the database actually chooses. Test it with representative data.

03Pay for the path and protect the right invariant

An index saves work only when its access path is cheaper than the alternatives. A filter that narrows five million rows to one ticket is highly selective. A filter matching most tickets is less selective; sequentially scanning the table may beat many scattered table lookups.

Estimate storage separately from read benefit:

1. Assume an average of 32 bytes per index entry, purely for planning.
2. Multiply 5000000 × 32 = 160000000 bytes, or 160 MB in decimal units.
3. Add space for pages, tree structure, and other engine overhead. The 160 MB is not a final storage guarantee.
4. Include maintenance work when inserts, deletes, or updates change indexed fields.

An index can also enforce a duplicate-key rule. Suppose an organization must not import the same external ticket identifier twice:

1. Store a unique key on (tenant_id, external_ticket_id).
2. Two concurrent imports may both try to insert the same pair.
3. The database’s uniqueness enforcement permits at most one matching key and rejects or resolves the conflicting insertion according to the operation.

That rule does not allocate identifiers, authorize readers, or include an external notification in the database transaction.

Review the full cost:

• Measure entries and rows visited, table lookups, and sorting for small and large tenants.
• Measure write response time and index size after adding it.
• Check the supported live-build procedure. Building an index reads and writes data while normal traffic continues.
• Before removing an apparently unused index, check rare critical queries and any constraint it enforces.

Choose the index whose measured read benefit is worth its storage and write cost.