Databases · Question 26 of 50

What is the N+1 query problem?

What they're checking

ORMs and performance in real code.

The answer

You load a list with one query, then run one more query for each item: one query for 100 posts, then 100 queries for their authors. Each is fast, but 101 round trips add up, and they grow with the data.

Fix it with a JOIN, or one batched query (WHERE id IN (...)), which ORMs call eager loading (include, select_related, prefetch_related); in GraphQL, a DataLoader batches the lookups. You can spot it in the query log: the same query again and again with different ids.

// N+1
const posts = await Post.findAll()                  // 1 query
for (const p of posts) p.author = await User.findByPk(p.authorId) // N more
// fixed
const withAuthors = await Post.findAll({ include: User })   // 1 query

The picture

N+1:    SELECT posts ─▶ SELECT user 1 ─▶ SELECT user 2 ─▶ ... ─▶ SELECT user 100
fixed:  SELECT posts JOIN users
        or: SELECT users WHERE id IN (...)
The follow-up they'll ask

Is a JOIN always better?

Show the answer

Not for big one-to-many results, because a join repeats each parent row. Two queries (the parents, then the children WHERE parent_id IN ...) are often better.

Your progress stays on this device.