Technology LearningAt some point a query that felt instant on a small table starts to crawl. The table grew, and the database is now reading every row to answer you. An index is the usual fix, and it can turn a slow query fast enough that it feels like a different machine. It helps to know what it actually is before you start adding them.
The book analogy that happens to be exact
Think of a textbook. To find every mention of a word without the index at the back, you would read every page. With the index, you flip to the word, and it tells you the exact pages. A database index is the same thing: a sorted lookup, kept to one side, that points straight at the rows you want.
Without an index, finding the rows where the patient identifier equals a value means scanning the whole table, row by row. This is called a full table scan, and on millions of rows it is slow. With an index on that column, the database jumps to the matching entries and reads only those rows.
Why it is fast
The index is kept in sorted order, so the database can find a value the way you find a name in a phone book: not by reading from the front, but by narrowing in, halving the search each step. Going from millions of rows to a handful of steps is where the speed comes from.
Why not index everything
If indexes are this good, why not put one on every column? Because they are not free.
- Every index takes storage, sometimes a lot.
- Every time you insert, update or delete a row, the database must update every index on that table too. Too many indexes make writes slower.
- An index the queries never use is pure cost and no benefit.
So an index is a trade: faster reads on the columns you search, in exchange for slower writes and more space. You index the columns you actually filter and join on, not every column you have.
Which columns earn an index
Good candidates are the columns that show up in your WHERE conditions, the columns you JOIN on, and the columns you frequently sort by. A primary key is indexed for you automatically. Foreign keys are very often worth indexing, because you join on them constantly.
How to know if it worked
Databases can show you their plan for a query, usually through a command called EXPLAIN. It tells you whether the database intends to scan the whole table or use an index. This is how you stop guessing: run EXPLAIN before and after, and see the plan change from a full scan to an index lookup.
Where to go next
Indexing is the highest-leverage performance skill in SQL, because one well-chosen index can matter more than any amount of query rewriting. Speeding It Up: Indexes & EXPLAIN teaches both the index and the tool that proves it is working, and Query Optimization covers the wider craft of making slow queries fast.