# Database index

> A sorted shortcut that lets a database find rows without reading the whole table.

- Level: Intermediate
- Topics: Data, Software
- URL: https://tidbit.legios.com.ar/database-index/
- In Spanish: [Índice de base de datos](https://tidbit.legios.com.ar/es/database-index/)

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 ana@example.com' 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.

## An analogy

The index at the back of a book. To find every page that mentions 'photosynthesis' you check the alphabetical list instead of reading the whole book; the price is a few extra pages that must be redone in every new edition.

## 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.

## Go deeper

- [Indexes, introduction (PostgreSQL docs)](https://www.postgresql.org/docs/current/indexes-intro.html)
- [Database index on Wikipedia](https://en.wikipedia.org/wiki/Database_index)

## Related terms

- [Database](https://tidbit.legios.com.ar/database/): An organized store of data that programs can search and update safely.
- [SQL](https://tidbit.legios.com.ar/sql/): The language you use to ask a database questions.