The N+1 Problem

The most common performance bug in app code

Picture typical backend code showing authors and their books:

authors = query("SELECT id, name FROM authors")   # 1 query
for a in authors:
    books = query("SELECT title FROM books WHERE author_id = ?", a.id)  # N queries

One query for the list, then one more per author. With 3 authors that is 4 queries. With 2,000 authors it is 2,001. This is the N+1 problem: the code looks innocent, works fine in testing with 5 rows, then melts in production, because every query pays network and planning overhead.

The fix is the tool you mastered in unit 5: one JOIN brings everything back in a single query, and the loop just formats rows it already has. Databases are extremely good at joins. Round trips are what kill you.

One query instead of one per row

A single join returns every author-and-book pair at once. The bookstore data here also becomes the capstone dataset for the final lesson.

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  author_id INTEGER,
  title TEXT,
  price INTEGER
);

INSERT INTO authors VALUES (1, 'Frank Herbert');
INSERT INTO authors VALUES (2, 'Isaac Asimov');
INSERT INTO authors VALUES (3, 'Ursula K. Le Guin');

INSERT INTO books VALUES (1, 1, 'Dune', 10);
INSERT INTO books VALUES (2, 2, 'Foundation', 9);
INSERT INTO books VALUES (3, 3, 'The Dispossessed', 12);
INSERT INTO books VALUES (4, 2, 'I, Robot', 8);
INSERT INTO books VALUES (5, 1, 'Dune Messiah', 11);

-- ONE query instead of 1 + N:
SELECT authors.name, books.title
FROM books
JOIN authors ON authors.id = books.author_id
ORDER BY authors.name, books.title;

Output

Frank Herbert|Dune
Frank Herbert|Dune Messiah
Isaac Asimov|Foundation
Isaac Asimov|I, Robot
Ursula K. Le Guin|The Dispossessed

The loop version would fetch five books and then ask for an author name five separate times, six queries in total for the same five lines of output. Here one round trip carries everything.

Nothing about the join is new. It is the same query from lesson 5-2, and that is rather the point: the cure for an N+1 problem is almost always a join you already know how to write.

Diagnosing 51 queries on a 50-item page

A page showing 50 products with their category names and issuing 51 queries has the classic N+1 shape: one query fetches the products, then one more query runs per product to fetch its category.

The number is the giveaway. 51 = 1 + 50, and a page whose query count tracks its row count is looping when it should be joining.

The cure is a single query:

SELECT products.name, categories.name
FROM products
JOIN categories ON categories.id = products.category_id;

Indexes and faster hardware make each round trip quicker, but the round trips themselves are the waste, and 50 of them cost 50 network latencies no matter how fast the database is. One join returns all 50 rows with their category names in a single trip.

Counting the queries on a 200-row dashboard

A dashboard that lists 200 customers and then runs one extra query per customer to count their orders issues 201 queries: one for the list plus 200 individual counts.

The name N+1 describes exactly that arithmetic, with N per-row queries plus the initial list query, and N is 200 here.

One query replaces all of them:

SELECT customers.name, COUNT(orders.id) AS order_count
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.name;

The LEFT JOIN matters, as lesson 6-1 showed, so that customers with no orders still appear rather than dropping off the dashboard. Counting orders.id rather than COUNT(*) is the matching detail: it counts real order rows and reports 0 for a customer whose joined row is all NULLs.