A database index: the table of contents of a book

A database index is to a database what the index at the back of a book is to a reader: a way to go straight to the right page without flipping through everything. Without one, the database has to read every row of a table to find the ones you want. With one, it goes directly there.

The analogy: searching a book

You are looking for the page about "invoicing" in a 400-page book. Without an index, you read from the beginning. With the index, you find the word, read the page number, and open the book there.

A database table is that book. Each row is a paragraph: a customer, an order, a message. When you ask for "this customer's orders", the database can either read the whole table or consult an index built on the "customer" column.

What happens without an index

The database reads every row to check whether it matches. It is invisible while the table is small. But as the data grows, the full read takes longer. A search that answered instantly on day one gets slow once the table has filled up, without a single line of code having changed.

What an index is, concretely

It is an extra structure, kept up to date by the database, that stores the values of a column in an order that makes them very quick to find, with a pointer to the full row. You build it on the columns you use to search, filter, sort or join tables: an email for a login, a customer id for their orders, a date for sorting.

The price to pay

An index is not free:

• it takes up space, since it is a second structure to store;
• it slows down writes a little, because every insert or update also has to update the index.

Think of a table of contents you would have to rewrite with every new page. Hence the rule: do not index everything, index what frequent searches actually use.

How to tell if one is missing

• A page or search that slows down as the data grows.
• A query that shows up among the slowest in your monitoring tools.
• The database can explain how it runs a query: if it reports reading the whole table for a targeted search, an index is probably missing.

An index is only one lead: if the same query is repeated hundreds of times, the problem is elsewhere, it is the N+1 problem. To avoid redoing a computation, you can also use a cache. For the full tour, see why an app is slow.

Key takeaway

An index lets you search the table of contents instead of the whole book. Put them on the columns you query often, without overdoing it, because each index costs space and slows writes slightly.

And for your product?

Missing indexes are among the things an outside eye spots quickly, since they show in the database structure and in the queries sent to it. A code audit of your app reviews them along with the rest. We can talk about it in a 30-minute call.