Database index
A sorted shortcut that lets a database find rows without reading the whole table.
A database index is an extra structure, kept next to a table, that lets the database jump straight to the rows you ask for. Without one, a query like 'find the customer with email [email protected]' forces a full scan: reading every row until it shows up. With an index on the email column, the database looks the value up in a sorted structure and goes directly to the right row.
Most indexes are a B-tree: a sorted, branching structure where each step narrows the search, so finding one value among a hundred million takes a handful of jumps instead of a hundred million reads. You create one with a line of SQL, CREATE INDEX ON customers (email);, and from then on the database keeps it up to date by itself and decides when to use it.
Indexes are not free. Each one takes disk space, and every insert or update has to update the indexes too, so writes get slower. The craft is choosing them from the queries the app really runs: the columns used to filter, join and sort. When a page that used to load instantly starts taking seconds as the data grows, a missing index is the first suspect.
Keep in mind
- An index trades disk space and slower writes for much faster reads.
- Without an index, the database has to scan every row to answer a query.
- Index the columns you filter, join and sort by, not every column.
- Most indexes are B-trees, which keep values sorted so a lookup takes a few steps.
