# Database indexes in plain words

> An index is a sorted lookup that lets a database find rows without reading the whole table. What it speeds up, what it costs, and how to tell whether a slow query needs one.

Source: https://hintvo.com/blog/database-indexes-in-plain-words | Last updated: November 20, 2026 | Reviewed: 2026-11-20

An index is a sorted lookup kept next to a table, like the index at the back of a book. Without it, the database reads every row to find the ones you asked for. With it, the database goes almost straight to them.

## What it speeds up

- Finding rows by a value: the user with this email.
- Finding a range: the orders from last week.
- Sorting by the indexed column.
- Joining two tables on a column.

## What it costs

- **Slower writes.** Every insert and update has to update each index as well.
- **Space.** An index is a second copy of some of the data.
- **Attention.** An index nobody uses still costs on every write.

This is why you do not index every column. You index the ones your queries search by.

## An example

```sql
-- slow on a large table: reads every order
SELECT * FROM orders WHERE customer_id = 42;

-- after this, the same query jumps to the rows
CREATE INDEX orders_customer_id ON orders (customer_id);
```

## Indexes on more than one column

An index on customer and date helps a query for one customer's orders in a period. The order of the columns matters. It works like a phone book sorted by surname and then by first name: it finds everyone called Kaya quickly, and Deniz Kaya quickly, but it is no help for finding everyone called Deniz.

## How to tell whether a query needs one

1. Ask the database to explain the query. Every database has a command that shows its plan.
2. Look for a step that reads the whole table.
3. Compare how many rows the query read with how many it returned. Reading a million to return ten is the sign.

## When an index does not help

- The table is small. Reading a hundred rows is already fast.
- The query returns most of the table anyway.
- The search wraps the column in a function, or starts with a wildcard, so the sorted order cannot be used.

## Questions you will hear in a meeting

- Is there an index on the column we filter by?
- Did we look at the query plan?
- Will adding this index lock the table?
- Is this index still used by anything?

> **Where Hintvo fits:** when a term like this comes up in a call, the In plain words card explains it in one sentence, in the context of your project. When someone asks how your own code handles it, the answer names the file and line.

## Related

- [The N+1 query problem in plain words](https://hintvo.com/blog/n-plus-one-queries-in-plain-words.md): What is the N+1 query problem, in plain words?
- [Database migrations in plain words](https://hintvo.com/blog/database-migrations-in-plain-words.md): What is a database migration, in plain words?
- [SQL interview questions: an approach that works for any query](https://hintvo.com/blog/sql-interview-approach.md): How do I approach SQL questions in a technical interview?
