A busy table has accumulated a dozen single-column and multi-column indexes and writes have slowed. How would you decide which multi-column index keys the workload actually needs, and which existing indexes are safe to drop as redundant?

## Why this is a judgment problem Every index is paid for on every write — an insert touches every index on the table, an update touches every index whose key columns changed (and in some engines every index, period), and a delete must remove or mark entries everywhere. Indexes also compete for buffer cache and lengthen maintenance operations. So the goal is the *smallest set of keys* that still gives every important query shape a good access path. ## Step 1 — inventory the demand, not the supply List the recurring query shapes from actual traffic: statement digests, slow-query capture, ORM query logs. For each, record the filter columns and predicate kind (equality / range / IN / expression), the sort and whether there is a `LIMIT`, and the call rate. Two shapes that differ only in constants are one family. Do not forget periodic jobs — a monthly reconciliation query that runs once but must finish before a deadline is a real shape. ## Step 2 — derive one key per family Apply the ordering rules: equality-constrained columns leftmost, then the single range column or the `ORDER BY` columns. Choose the order among the equality columns so that useful **sub-prefixes** land on other shapes — this is what turns N indexes into one. A key `(tenant_id, status, created_at)` simultaneously serves `tenant_id = ?`, `tenant_id = ? AND status = ?`, and the tenant+status+time-range shape, and can supply `ORDER BY created_at` inside a pinned tenant+status. ## Step 3 — prefix subsumption The mechanical part. An index whose key is a leftmost prefix of another index's key is redundant for lookup purposes: `(a)` ⊂ `(a, b)` ⊂ `(a, b, c)`. Every seek the shorter key supports, the longer key supports identically, because both descend on the same leading columns. Three honest caveats: 1. **Not a prefix, not redundant.** `(b, a)` and `(a, b)` are different structures serving different single-column shapes. 2. **Width costs something.** The longer key is physically wider, so full scans of it read more pages and it has lower fanout. If a hot query does a large scan of the short index, keeping it can be justified — measure rather than assume. 3. **Constraint-backing indexes are not droppable.** A unique index enforces a constraint; dropping it changes semantics, not just performance. Primary-key and unique indexes come off the candidate list immediately. ## Step 4 — check usage evidence Every mainstream engine exposes per-index usage counters (scans/seeks and last-used information). Reset or snapshot them, then observe across a **full business cycle** — a week is usually not enough if month-end reporting exists. Zero scans over a full cycle is strong evidence, but combine it with the static prefix analysis so you understand *why* something is unused. Also check replicas: an index unused on the primary may be the one carrying the reporting workload on a read replica. ## Step 5 — roll out safely Drop one index per change window, most-clearly-redundant first, and keep the exact `CREATE INDEX` statement ready to restore. Watch p95/p99 latency for the affected shapes and the plan for the queries you predicted would move onto the surviving key. Where the engine supports it, marking an index invisible/disabled before dropping gives a reversible test. Create replacement indexes concurrently/online before dropping the ones they replace, never after. ## Step 6 — set a norm so it does not regrow The dozen indexes appeared because each was added in isolation to fix one query. The durable fix is a review rule: any new index proposal must state the query shape it serves and show that no existing key's prefix already covers it. Keep the index set documented next to the schema so the next engineer extends a key rather than adding a sibling. ## What a strong answer sounds like Evidence first, derive keys from shapes, apply prefix subsumption mechanically, exclude constraint-backing indexes, validate against a full cycle of usage counters, roll out reversibly, and close with the process change that stops the sprawl from returning. Acknowledging the write-cost-versus-read-benefit tradeoff explicitly — including that some redundancy is a deliberate purchase — is what separates this from a checklist.