2.How the Database Actually Finds a Row

Sarah pulls a real book off the shelf behind the counter and flips to the back page. "When you look up a word in here, you don't read every page front to back. You use the index — it tells you exactly which page to go to."

Mike nods slowly. "So the database... doesn't have one of those?"

"Not unless we build it one," Sarah says. "Right now, every single lookup means checking every single row. We can give it an actual shortcut."

An index is a separate structure the database keeps, sorted by the column(s) you tell it to track — built specifically so it can jump straight to matching rows instead of checking every one. It costs extra space and a little extra work on every write, in exchange for dramatically less work on reads.

Almost every database — SQLite included — builds its indexes as a B-Tree: a balanced, sorted tree structure where every lookup starts at one root node and narrows down in just a handful of steps, no matter how large the table gets.

That's the entire trick: instead of one giant pile of rows in no particular order, a B-Tree keeps things sorted and branching, so each step through it eliminates a huge chunk of the remaining possibilities — a handful of comparisons replaces thousands of individual row checks.

But an index isn't free. It takes real disk space, and every write to the table now has to update the index's sorted structure too. That trade-off — faster reads in exchange for slightly slower writes and more storage — is exactly what the next chapter digs into, because it's why you can't just index every column and call it done.