What is a database index and why does it make queries faster?

An index is a separate, sorted data structure that lets the database find rows without scanning the whole table. It's the same idea as the index at the back of a textbook: instead of reading every page to find 'photosynthesis', you look it up in an alphabetical list that points you straight to page 214. Without an index, a query like `WHERE email = 'x@y.com'` forces a full table scan — the database reads every single row and checks. At a thousand rows that's imperceptible. At ten million it's the difference between 2ms and several seconds, which is why the improvement felt like magic. With an index on `email`, the database consults a sorted structure (typically a B-tree) and narrows down in a handful of steps rather than millions of comparisons. Roughly, scanning is proportional to table size; an index lookup grows logarithmically, which is why it stays fast as data grows. What indexes actually cost, because they're not free: 1. Write speed. Every insert, update and delete must also update every index on that table. A table with eight indexes writes noticeably slower than one with two. 2. Storage. Indexes are real data on disk, sometimes a substantial fraction of the table size. 3. Maintenance complexity. Unused indexes are pure overhead, and they accumulate quietly. So the guidance is: index what you filter, join and sort on — not everything. What's usually worth indexing: - Foreign keys (the columns you join on). Frequently missed, and a huge cause of slow joins. - Columns in `WHERE` clauses on large tables, especially high-selectivity ones like email or username. - Columns you `ORDER BY` on large result sets. - Combinations queried together — a composite index on `(user_id, created_at)` serves 'this user's recent posts' far better than two separate indexes. Column order matters: it can be used for `user_id` alone, but not for `created_at` alone. What's usually not worth it: very small tables (scanning is already fast), and low-selectivity columns like a boolean flag where each value matches half the table — the database may reasonably ignore the index anyway. The tool to learn: `EXPLAIN` (or `EXPLAIN ANALYZE`) in front of your query. It shows you the plan the database chose — whether it used a scan or an index, and the estimated cost. That turns performance work from guessing into reading. If you take one habit from this, make it running `EXPLAIN` on any query that feels slow before adding anything.