Database Index
An auxiliary data structure that speeds selected database lookups and ordering at the cost of storage and additional write work.
Download Markdown (.md)Soroush’s Product Wiki · https://wiki.soroush.uk/topic/database-index/
01What it means
A database index is a maintained structure that helps a database locate rows or values without scanning all stored data. An index is built over one or more expressions or fields and may support equality, range, ordering, uniqueness, or specialized searches depending on its type and database.[1][2]
- 01Capture queryParameters, volume, frequency
- 02Inspect planScans, joins, sort, selectivity
- 03Design indexFields, order, type, predicate
- 04Create safelyAccount for table size and traffic
- 05Measure tradeoffsReads, writes, storage, usage
Faster reads trade against write cost, storage, memory, and maintenance; an unused-looking index may still enforce a constraint.
02Why it matters
The right index can turn an unusably slow product operation into a predictable one and can enforce uniqueness. Each index also consumes space, slows writes, and requires maintenance, so indexing is a workload decision rather than a universal optimization.
03When to use it
Add an index when measured queries repeatedly filter, join, order, or enforce uniqueness on suitable data and the read benefit outweighs write and storage cost. Avoid speculative indexes on small tables, rarely used paths, or frequently changing low-selectivity values unless plans show value.
04How it works
- Capture the slow or important query together with representative parameters, data volume, and frequency.
- Inspect its execution plan to identify scans, joins, sorting, selectivity, and row-estimate problems.
- Choose index fields, order, type, predicates, or included data to match the access pattern.
- Create the index using an operational approach appropriate to table size and live traffic.
- Measure read improvement alongside write latency, storage, memory, and maintenance effects.
- Monitor usage and remove redundant or unused indexes after confirming they serve no constraint or query need.
05A practical example
An order-history query filters by account and sorts by creation time. An index on account identifier followed by creation time lets the database retrieve the newest matching orders directly, while measurements confirm the extra write cost is acceptable.
06Where it breaks
Indexes do not repair incorrect queries, poor models, network latency, or returning excessive data. A database may ignore an index, data distribution can change its value, and too many overlapping indexes increase write amplification, storage, backup size, and maintenance work.
Common mistakes
- Adding indexes to every column without examining real queries and plans.
- Assuming the database will use an index merely because a queried field is included.
- Ignoring field order, selectivity, predicates, and sorting in a composite index.
- Measuring only faster reads while overlooking write latency, locks, storage, and maintenance.