How database indexes actually work: B-trees, selectivity, composite indexes, covering indexes, and reading EXPLAIN plans so your queries scale past 10 million rows.
The Query That Worked Fine Until It Didn't
Every developer has a story about the query that worked perfectly in development and crawled to a halt in production. The table had 10,000 rows in your local database; in production it has 10 million. The query that took 5 milliseconds now takes 45 seconds. The dashboard times out. The support tickets arrive.
The culprit is almost always the same: a full table scan. The database engine reads every single row in the table, one by one, looking for the ones that match your WHERE clause. On a table with 10 million rows, that means reading millions of disk pages — work that could have been avoided with the right index.
An index is a separate data structure that the database maintains alongside your table. It works like the index at the back of a textbook: instead of reading every page to find every mention of "normalization," you look up the term in the index, which points you to pages 42, 87, and 156. Database indexes do the same thing, but with a twist that catches many developers off guard: an index is only useful if the query planner decides to use it. And the planner's decision depends on a single number: selectivity.
