Database Index

Without an index, a database has no idea where a value lives. It reads every row from the first to the last and checks each one against the condition. With thirty rows nobody notices. The trouble starts once rows pile up.

In Pagedeck's first year the documents table held 30,000 rows and search returned in a tenth of a second. Two years later the same table held 2 million rows and the same search took more than six seconds. Not a line of code had changed and the server was the same machine — there were simply sixty times as many rows to read. Support tickets started arriving that said people hit search and then waited.

Two fixes come to mind at that point: store less data, or buy a bigger machine. The first is out when nothing is safe to delete, and the second costs money while putting you back where you started once the table grows another tenfold. An index is the third route. The data stays, the server stays, and only the path to the row changes.

The database keeps the values you search on in sorted order in a separate structure, with a pointer next to each value saying which row it came from. It works like the index at the back of a book: instead of turning every page, you look up the word and jump straight to the page number printed beside it.

Building one takes three steps.

1. Pick the column. A table stores data in rows and columns, where one column is a single field such as an email address or a signup date. You pick a column that shows up in search conditions often.
2. The database sorts the values in that column into its own structure and attaches the location of the original row to each value.
3. When a query arrives, the database narrows the sorted structure by halves until it lands on the value, then reads only the rows those pointers name.

The payoff grows with the table. Scanning 2 million rows means 2 million comparisons; halving a sorted structure gets there in roughly twenty. That gap is what turns six seconds into a hundredth of a second, and it is why an index that seems pointless on a small table becomes the difference between a usable page and an unusable one later.

There is a price. Every insert or update has to change the index as well as the table. Ten indexes on one table means eleven places to update for every row written, and each index takes its own disk space. Reads get faster and writes get slower, so indexes go on columns that genuinely appear in conditions.

Some indexes never get used. A column with only two possible values doesn't help much, because even with an index you still end up reading half the table. A condition that transforms the column before comparing can't use the index either, since what got sorted was the raw value, not the transformed one.

Indexes tend to land in four places.

1. Lookups by condition — the columns that appear in the filter of a SQL query. Finding a member by email address, or searching documents by title, lives here.
2. Joining tables — a foreign key is a column that points at a row in another table, and attaching member names to an order list means matching the two tables on that column. Without an index on the matched side, one of the tables gets scanned end to end.
3. Sorting and date ranges — the list that shows the twenty most recent items, or the query that pulls last month's signups. Sorted order is already there, so the database reads in order and stops as soon as it has enough.
4. Uniqueness — the guard that stops the same email address from registering twice is built as an index too. A primary key, the column that identifies one row uniquely, usually gets an index automatically.

The places to skip are just as clear. A settings table with a few hundred rows reads in full instantly, and a log table that takes nothing but writes is only slowed down by indexes.

The hard case is an object-relational mapper (ORM). An ORM connects objects in code to database tables, so you don't write the query yourself — which also means the code never shows you which column ends up in the condition. There you read the database's slow query log to see the statement that actually ran.

"This query is slow and I don't think it's using the index."

There is an index on the column, but the database is ignoring it and reading the whole table anyway.

"Indexing that column speeds up reads but slows down writes."

Naming the trade rather than the benefit, usually leading into a question about how write-heavy the table is.

"Let's add an index first and measure again."

Try the cheapest, most reversible fix before restructuring anything.

This is what Theo did when Pagedeck's search crept up to six seconds. The slow query log showed a statement scanning all 2 million rows of the documents table, running 40,000 times a day. An index on the title column brought the same statement down to two tenths of a second. Nothing in the application changed — only the shape of the table.

He ran it overnight. Sorting 2 million existing rows into a new structure can hold up writes to that table while it runs, so he warned Mia in advance that slow search would be fixed that night and that saving a document might briefly lag.

The most common belief is that more indexes are better. Once you watch a query get twenty times faster, adding one to another column feels obvious, and since it takes a single line nobody objects. But an index consumes disk and gets rewritten on every write. Stack up ten indexes nothing queries and reads stay exactly where they were while writes visibly degrade — then months later somebody asks why saving is so slow and no one can answer.

The other mistake is expecting an index to fix any slow page. It's a tool that worked dramatically once, so it gets reached for again. But a pattern like the N+1 problem, where the issue isn't one slow query but 101 separate ones, doesn't shrink with an index. Each of the 101 gets faster while the count stays the same, and the page is still slow. Keep adding indexes in that situation and you trade away write performance without ever finding the cause.