# Indexes

Which indexes a schema carries, decided by data. Every index taxes every write to its table and earns its keep only through the reads it serves, so the decision weighs the two from ledgers. A pre-production service models them, and a production service measures them. The traffic-independent index rules are the `nustack` skill's `database.md`.

## The two ledgers

- **The read ledger:** one row per index. It lists every statement that can use the index (`file:line`), the activity behind it, the cache in front of it, and how often the database runs it per month at the planning scale, after the caches. It also states what the statement would examine without the index: the next-best index prefix or a full scan, as a row count at that scale.
- **The write ledger:** one row per table. It gives inserts, deletes and updates per month by activity, and for each update, which index key columns it changes. An index is maintained by every insert and delete on its table, and only by the updates that change its key columns.
- **The planning scale:** the active people, tenants and rows expected a year out, stated once and used for every count. A verdict that flips within ten times that scale names the scale at which it flips, which is its trigger to revisit.

## Where the numbers come from

### A pre-production service: synthetic

- **Reads:** the synthetic workload (`workload.md`), carried through the caches to the statements the database runs at each temperature.
- **Writes:** a write model beside the call mix. It gives the monthly rate of every activity that writes, per active person: sign-ups, profile and email edits, password changes and resets, sign-ins and sign-outs, enrolments, administrative edits, and each background flush and purge. Every rate is an assumption labelled as one, and the sensitivity line says which rate moves a verdict.
- **Rows examined:** a formula from the planning scale, such as "the rows of one tenant in `orders`". Where a verdict rests on it, seed a synthetic dataset at the planning scale through the seeding pipeline and replace the formula with `EXPLAIN ANALYZE`.

### A production service: measured

Statistics reset when the server restarts, so a reading covers a stated window, and a verdict needs a window holding a full business cycle: at least a month, month-end jobs included. Read every replica, because an index serving only a replica's reporting query looks unused on the primary.

| Engine | Reads per index | Writes per table | Statements |
| --- | --- | --- | --- |
| MySQL 8 | `performance_schema.table_io_waits_summary_by_index_usage` (`COUNT_READ`, with the `INDEX_NAME IS NULL` row counting full-scan reads); `sys.schema_unused_indexes` | the same view's `COUNT_INSERT`, `COUNT_UPDATE`, `COUNT_DELETE` | `events_statements_summary_by_digest` (`SUM_ROWS_EXAMINED` against `SUM_ROWS_SENT`); `sys.statements_with_full_table_scans` |
| Postgres | `pg_stat_user_indexes.idx_scan` | `pg_stat_user_tables` (`n_tup_ins`, `n_tup_upd`, `n_tup_hot_upd`, `n_tup_del`, `seq_scan`, `seq_tup_read`) | `pg_stat_statements` |
| SQLite / D1 | none kept | none kept | `wrangler d1 insights` (rows read and written per query); `EXPLAIN QUERY PLAN` |

The window's start is MySQL's `Uptime` or Postgres's `pg_stat_database.stats_reset`.

## The decision

Run in order. An earlier step that keeps an index ends its review.

1. **An invariant a writer relies on keeps its unique key.** The reliance is cited: the write path that catches the duplicate-key error, the upsert that depends on the key, or the race that a check before the insert would lose. A unique key nothing relies on is a read index and goes through the steps below.
2. **A foreign key gets its index from a read.** InnoDB requires an index with the key's columns leading. An index the reads already justify supplies it, reordered or extended if needed, before a standalone one is added.
3. **A read index stays when its reads save more than its writes cost.** The defaults by how the read runs, per month at the planning scale, each overturned by a measurement:
   - **A hot request-path read** (about 1,000 database runs or more, or on the page-load path): index when the next-best path examines more than about 100 rows.
   - **A rare request-path read** (a console, settings or admin page under about 1,000 runs): index only when the next-best path examines more than about 10,000 rows. A tenant-scoped range scan under that answers in milliseconds.
   - **A background job** (hourly or daily): no index, unless the scan without one examines more than about a million rows or holds locks hot traffic waits on. Drive the job through an index it already has, or through a column that never changes.
   - **An unindexed delete or update locks what it scans.** Under REPEATABLE READ, the MySQL default, a `DELETE … WHERE` on an unindexed column takes a next-key lock on every row it examines, and hot writes to those rows wait. A job dropping its index therefore selects the ids with a plain non-locking read, then deletes one id per statement, repeating the predicate in the `DELETE` so a row changed in between survives. An `id IN (…)` list is no safe batch: the optimizer may plan it as a full scan, which locks every row again. On MySQL 8.4, 5,000 ids over 6,000 rows did, and so did 100 ids over 303. A single id is always a primary-key lookup.
   - **A read about once a week or rarer:** no index. A month of its scans costs less than a day of the index's maintenance.
4. **A key column written on a hot path costs its index a delete and an insert per write.** When an index's maintenance writes exceed the database reads it serves by about ten times, throttle the write, move the read's predicate to a column that never changes, or drop the index. Example: a session's sliding expiry rewritten on every refresh is rewritten only once half its lifetime has passed.
5. **An index for a read that does not exist yet is not built.** Adding one later runs online: `ALTER TABLE … ADD INDEX … ALGORITHM=INPLACE, LOCK=NONE` on MySQL 8, `CREATE INDEX CONCURRENTLY` on Postgres. Speculation saves nothing a later migration cannot buy.
6. **On measured data, an index with no reads across the full cycle goes,** unless step 1 or 2 keeps it. A request-path statement examining more than about 100 rows per row it returns earns one.

## Collapsing an index

- **Extend before adding:** when a second index exists only to add a column the first lacks, append the column to the first and drop the second. The reads ordered by the first keep their order, and the column's filter is evaluated inside the index. Example: `(tenant_id, created_at, id)` plus `(tenant_id, status)` becomes `(tenant_id, created_at, id, status)`, so status counts stay index-only.
- **Fold a foreign key's index into a unique key:** a child's `(parent_id, tenant_id)` index that only a foreign key uses folds into a unique `(parent_id, slug)` as `(parent_id, tenant_id, slug)`. The pair stays unique because the tenant is determined by the parent.

## Reads in pairs

- **Two tables, one call:** a one-to-one pair that every call reading one also reads costs two statements where one would do. The read ledger shows them as statements that always run in the same call, and they merge into the owner (`techniques.md`, Merged tables).
- **Two indexes, one read:** two indexes serving one read pattern from different leading columns collapse into the one composite every statement can use. Each statement is re-checked against it, because the leftmost prefix decides what it serves.

## The index verdict

The verdict (`judging.md`) carries one row per index: kept, dropped, changed or added. Each row cites the rule that decided it, the monthly reads served and writes taxed, and the rows the next-best path examines. A borderline verdict also names the scale at which it flips.