If a book has an index at the end, you do not have to read the whole book to find a specific topic. A database index is exactly that. Without an index, the database has to look at a whole table one row at a time to find a user - that is called a full table scan. With 100 rows in the table there is no problem; with 1 million rows every query takes several seconds.
Indexes work on a B-tree structure - because they are sorted, searching happens by jumping, not one by one. The columns that get searched the most (that appear in WHERE), that JOIN on, that sort on - those are the ones that need indexes. Login happens by email? Index on email. Orders always come by user_id? Index on user_id.
So why not index every column?
No - this is where the judgement lies. Every index takes separate space, and every insert or update has to update all the indexes too. With too many indexes, reads get fast but writes get slow. So the rule is: understand the read pattern, and index sparingly, by measurement.
EXPLAIN - look at proof, not guesses
Instead of guessing why a query is slow, run EXPLAIN and look. The database itself tells you which path it is taking - whether it is using an index, or scanning the whole table (if you see type: ALL, that is the suspicion).
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- Adding an index
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Composite index over multiple columns
CREATE INDEX idx_orders_user_status ON orders(user_id, status);Connection pooling - another hidden cost
Opening a new connection to the database is expensive work. If every request opens and closes a new connection, the server quickly comes under pressure. Pooling means keeping a fixed number of connections ready and sharing them round-robin - Prisma or any good driver handles this on its own, you just have to tune the pool size to the need.
The mistake everyone makes
Two extreme mistakes show up: giving no indexes anywhere (the site slows down as soon as users grow after launch), and giving indexes everywhere (slow writes, more space). Another mistake - optimizing by guesswork without looking at EXPLAIN. Measure first, then fix.
Today's practice
Deliberately insert ten thousand rows into yesterday's orders table (with a small script). Then run a query by user_id and look at the time, add an index, and look at the time again. Put the two EXPLAIN outputs side by side and read the difference yourself.
How to say it in an interview
An index is a sorted list, so the database can reach a row directly without scanning the whole table. I put indexes on the columns that get searched, joined and sorted on the most - but sparingly, because every index slows down writes. I catch slow queries with EXPLAIN, not with guesses.