Why some questions are cheap and some are ruinous
Indexes explained properly, plus the query pattern that quietly destroys apps.
Without help, answering "find the user with this email" means reading every row until a match is found. With ten rows that is instant. With ten million it is a disaster. An index is the help.
That last clause is the part people miss. An index on email does nothing for a search by phone number. Every index also has to be updated on every write, so indexing every column makes reads fast and writes slow. Indexes are a deliberate trade, chosen per query pattern.
The N+1 query problem
This one deserves its own name because it is so common and so invisible in development. You fetch a list of 50 posts: that is one query. Then, for each post, your code fetches the author. That is 50 more queries. You wrote what looked like a simple loop and issued 51 round trips to the database.
- In development
5 posts, local database, 6 queries, 8ms total. Feels fine. - In production
500 posts, database over the network, 501 queries, 12 seconds. - Under load
Every visitor does this at once. The database saturates and everything stops.
How many database round trips does this page make?
It is a question with a countable answer, and the count should not grow with the number of items on the page.
What to remember
- An index makes one kind of lookup fast, not all lookups.
- Indexes cost write speed and storage, so they are chosen, not sprinkled.
- A database call inside a loop is the N+1 pattern and it scales terribly.
Terms in this lesson
Show field notes toggles a search param the loader reads. With it off, the slow promise is never created, so nothing streams.