Free Indexes, queries and scale lessonsIndexes, queries and scale · Stage 2 · Executing queries · Step 2
N+1 queries and finding what's slow
The access patterns that make an app slow even with good indexes, and a method for diagnosing a slow query. About 12 minutes.
A report page makes 1 query to list 200 products and then 1 query per product for its category. What is the best fix?
- AFetch the categories with a join, or one IN query for all of them
- BAdd an index on category.id
- CMake the database server faster
Show the answer
Fetch the categories with a join, or one IN query for all of them. It replaces 201 round trips with 1 or 2.
4 questions on this idea
Short questions that apply the idea to new cases: trace it, work out a number, put steps in order. A wrong answer explains itself.
Answer them in your browserFree account, no card. Your progress is saved, and the idea comes back in a quick review later.
Sign up free to solve