The N+1 Query Problem, Measured: 2,001 Queries vs One JOIN

What is the N+1 query problem?

You run one query to get a list (the 1), then loop over the results and run one query per row to get related data (the N). With 2,000 authors that is 2,001 queries to build one page.

# N+1
for author in authors:
    books = db.query("SELECT title FROM books WHERE author_id = ?", author.id)

How bad is it?

I measured it in SQLite with 2,000 authors and 20,000 books, best of five runs:

Approach	Queries	Time
N+1	2,001	47.7 ms
One JOIN	1	2.8 ms

That is about 17x. The full setup is on the benchmark page.

Why will production be worse?

SQLite ran inside the process, so each of those 2,001 queries cost almost no network time. With a database on another host, every query adds a round trip. At even one millisecond per round trip, 2,001 queries cost about two seconds before any query work. Treat the 17x as a lower bound for the pattern, not a prediction.

How do I fix it?

Use one set-based query:

SELECT a.name, count(b.id)
FROM authors a LEFT JOIN books b ON b.author_id = a.id
GROUP BY a.id;

In an ORM, use eager loading (a join or a second batched IN query). Then confirm with your query log that the count dropped.

What else matters?

An index on the join column matters too. A lookup on 5 million rows took 403 ms with a full scan and 0.06 ms with an index in my test (details). Fixing the query count and the indexes together is where the large wins come from.