What's happening?
- Without an index, `WHERE email = …` has to read every row — a full table scan.
- With an index on email, the database reads the B-tree's root, follows one branch to a leaf, and finds a pointer to the row.
- A B-tree is very wide and shallow, so even a million rows need only three or four page reads.
Complexity
- Time
- O(log n) lookup with an index · O(n) full scan without
- Space
- Extra storage for every index, updated on every write
Where you'll meet it
Every login (WHERE email = ?), every "my orders" page (WHERE user_id = ?), and every slow-query fix starts with checking which index the query uses (EXPLAIN).
Common mistake
Indexing every column. Each index speeds up some reads but slows every INSERT and UPDATE, because the index has to be updated too.
FAQ
Why didn't the full scan stop at the first match?
Without a UNIQUE index the database can't know there is only one match, so it keeps reading.
What does EXPLAIN show?
Whether a query uses an index ("Index Scan") or reads the whole table ("Seq Scan"), and roughly how many rows it expects to read.
When does an index not help?
When the query reads most of the table anyway, wraps the column in a function (WHERE lower(email) = …), or filters on a column the index doesn't lead with.