SQL / INDEXES AND QUERY PERFORMANCE
The N+1 problem and fetching related rows together
Recognise a query-per-parent loop, replace it with one JOIN or one batched IN fetch, and know when aggregating beats joining.
What you will learn
- Spot N+1 by counting statements per request, not rows: 1 parent query plus N child ones.
- Rewrite a per-parent query loop as one LEFT JOIN or one batched WHERE fk IN (...).
- Use COUNT(o.id), not COUNT(*), when a LEFT JOIN can produce NULL child rows.
- Index the child's foreign key column so batched child fetches stay cheap.
Understanding The N+1 problem and fetching related rows together
N+1 is a shape, not a slow query: one statement returns N parent rows, then the code loops over them and issues one child statement per row, so 200 customers mean 201 statements. Every statement carries costs that have nothing to do with how many rows it returns — a round trip to the server, parsing, plan lookup, snapshot or lock bookkeeping, and a result set to decode. Those fixed costs are why 201 statements returning three rows each are far slower than one statement returning 600 rows, even though the same data crosses the wire. Tuning the individual child query cannot rescue this, because the problem is the number of statements, not the speed of any one of them.
The fix is to decide the whole set of parents first, then fetch their children in one go. A join does it in a single statement: the engine matches child rows to parents internally, probing an index on the child's foreign key or hashing one side depending on the engine, and it does that once instead of 200 times. The alternative is two statements — the parent query, then SELECT ... WHERE customer_id IN (collected ids) — with the application grouping the flat rows into lists by foreign key. That form keeps the statement count at two no matter how many parents you loaded, and it does not repeat the parent columns, which a join does once per child row.
Which rewrite wins depends on fan-out. Joining is best when each parent has a handful of children and you want everything in one pass; when a parent has thousands of children the join copies its columns across all of them and ships far more bytes than the two-query form, and if you only need a number, COUNT or SUM in SQL beats sending the rows at all. Two traps come with the rewrite: an INNER JOIN drops parents that have no children, which the loop version never did, and pulling two unrelated child tables into one join multiplies their rows against each other. Both follow from the same fact — a join returns one row per matching combination, not one row per parent.
CREATE TABLE customer (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customer(id),
total_cents INTEGER NOT NULL
);
INSERT INTO customer (id, name) VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Linus');
INSERT INTO orders (id, customer_id, total_cents) VALUES
(10, 1, 1200),
(11, 1, 350),
(12, 2, 9900);
CREATE INDEX orders_by_customer ON orders (customer_id);
-- The N+1 shape: one query for the customers, then one query per customer id
-- SELECT id, name FROM customer;
-- SELECT id, total_cents FROM orders WHERE customer_id = 1;
-- SELECT id, total_cents FROM orders WHERE customer_id = 2;
-- SELECT id, total_cents FROM orders WHERE customer_id = 3;
-- One statement instead, and it keeps customers that have no orders
SELECT c.id AS customer_id, c.name, o.id AS order_id, o.total_cents
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;Database time is paid per statement, not per row, so fetch every parent's children with one set-based query instead of one query per parent.
Worked examples
Two statements instead of 201
Collect the parent ids from the first query and fetch all children with a single IN lookup.
-- Query 1 (already run): SELECT id, name FROM customer ORDER BY id; -> ids 1, 2, 3
-- Query 2: every order belonging to those ids, in one statement
SELECT id, customer_id, total_cents
FROM orders
WHERE customer_id IN (1, 2, 3)
ORDER BY customer_id, id;Example explained
Line 1The IN list grows with the page size, but the statement count stays at two.
Line 2No parent columns are repeated, so this transfers less than the equivalent join.
Line 3Customer 3 has no orders and produces no rows, so the caller must default it to an empty list.
Line 4ORDER BY customer_id, id lets the caller build each list in one pass over the rows.
Aggregate instead of returning child rows
When only totals are needed, group in SQL so each parent comes back as exactly one row.
SELECT c.id, c.name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total_cents), 0) AS lifetime_cents
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;Example explained
Line 1LEFT JOIN keeps Linus, so all three customers survive the grouping.
Line 2COUNT(o.id) skips the NULL that the unmatched join produced, giving 0; COUNT(*) would report 1.
Line 3SUM over zero matching rows returns NULL, so COALESCE makes lifetime_cents a number for every row.
Line 4One statement replaces a count query and a sum query per customer.
Important notes
N+1 is a round-trip problem, so it hurts most when the database is across a network; the same loop against a local file database can look fine in development and collapse in production.
If the application groups joined rows as it streams them, order by the parent key first (ORDER BY c.id, o.id) — otherwise one parent's child rows can arrive interleaved with another's.
Common mistakes
Turning the loop into an INNER JOIN: customers with no orders vanish from the result instead of appearing with an empty list, so rows go missing without any error.
Pulling two independent child tables into the same join: 2 orders and 3 addresses become 6 rows, so SUM(total_cents) triples. Fetch each collection with its own batched query.
Pasting the collected ids straight into WHERE id IN (...): a few thousand ids can exceed parameter limits (SQLite allows 999 bound parameters by default) and string-building invites injection. Bind the values and chunk the list.
Try it yourself
Change, predict, then run
Insert a fourth customer with no orders, then write one query that lists every customer with their order count and total spend, and confirm both order-less customers appear with 0. Change LEFT JOIN to JOIN and note exactly which rows disappear.
Open the SQL workspaceCheck your understanding
An endpoint loads 200 products and then runs SELECT * FROM review WHERE product_id = ? once per product. You add an index on review.product_id, each of those lookups gets ten times faster, and the endpoint is still slow. What best explains that?
- Indexes only help queries that return many rows, and each review lookup returns only a few.
- SELECT * cannot use an index, so every review query still scans the whole table.
- The request still sends 201 separate statements, and the per-statement cost — round trip, parse, plan, result handling — now dominates.
- The index on review.product_id is ignored because product_id is a foreign key rather than a primary key.
Show answer
Speeding up each of 201 statements leaves 201 of everything that surrounds them: latency, parsing, planning and result decoding, and that overhead is independent of how fast the row lookup is. Option 2 is tempting but wrong twice over — SELECT * can still use review.product_id to find the rows, and even a perfect covering index would not remove a single round trip.