Lesson 21 / 32

Database Indexing

Trading write cost and storage for fast lookups.

What an index is

An index is a sorted side-structure (usually a B-tree) pointing from a column's values to row locations, so the database can binary-search instead of scanning every row.

The write cost

Every index must be updated on insert/update/delete, so more indexes mean slower writes and more storage. Index the columns you actually filter, join, or sort on — not everything.

The book index

A database index is like the index at the back of a textbook: it costs pages (storage) and someone has to update it whenever content changes (writes), but it turns 'find every mention of X' from reading the whole book into one lookup.

Composite index order matters

An index on (country, city) speeds up queries filtering by country alone or by country+city, but not by city alone — the leftmost column must be used. Order composite indexes by your actual query patterns.