Databaseseasy

What does an index do and what is the main trade-off?

Answer

An index speeds up reads (filtering/sorting) by avoiding full table scans. The trade-off is slower writes (INSERT/UPDATE/DELETE) and extra storage because the index must be maintained.

Advanced answer

Deep dive

Indexes are additional data structures (often **B-trees**) that let the database find rows without scanning the whole table. They help with:

`WHERE` filters,
`JOIN` lookups,
`ORDER BY` / `GROUP BY` (sometimes),
uniqueness enforcement (unique indexes).

The main trade-off

Every write must update all relevant indexes, so:

writes get slower,
storage increases,
maintenance work grows (stats/vacuum/reindex depending on DB).

Practical tips

Prefer indexes on columns with good selectivity (many distinct values).
For composite indexes, column order matters (left-prefix rule): index on `(a, b)` helps queries filtering by `a` (and by `a` + `b`), but not just by `b`.
Avoid redundant indexes (they add write cost without benefit).

Common pitfalls

Indexing boolean/low-cardinality columns and expecting big wins.
Too many indexes on a hot write table.
Not verifying with `EXPLAIN` whether the planner actually uses the index.