PHP
The N+1 query problem
Learn the N+1 query problem in ORM and data loading and the eager loading solution.
What is the N+1 query problem?
When you list N rows in an ORM app and fetch each row's related data in a separate query, you have the N+1 problem: 1 query for the rows, N queries for their relations. A hundred rows run a hundred and one queries; a thousand rows, a thousand and one.
Eager loading fixes this: it fetches related data in one query with a JOIN or an IN clause, dropping the total back to one. Lazy loading is the default and produces queries on demand; use eager for lists and lazy for single items.
Eager loading in practice
Eager loading caches the relation data once. You first fetch all posts, collect their author_id values with array_column, run a single SELECT ... WHERE id IN (...) to fetch all authors, and store them in a lookup table keyed by id.
When printing, each post looks up its author name from the table; the total query count drops to 1 + 1 = 2 (instead of N+1). This pattern is also called a batch query and is done in ORMs with with() or join() calls.
Exercise Compare N+1 and eager loading with counts
The $posts and $authors arrays below are ready. Start with $queryCount = 0. Simulate lazy (N+1): in a foreach over $post increment $queryCount++ and read $author = $authors[$post['author_id']]. Print echo $queryCount . "\n". Eager approach: $queryCount = 1 and echo $queryCount . "\n". Then foreach over the posts and print each 'title'.
Show example solution
Start with $queryCount = 0;. foreach ($posts as $post) { $queryCount++; $author = $authors[$post['author_id']]; } echo $queryCount . "\n"; $queryCount = 1; echo $queryCount . "\n"; foreach ($posts as $post) { echo $post['title'] . "\n"; }.
Example solution:
<?php
$posts = [
['id' => 1, 'title' => 'A', 'author_id' => 10],
['id' => 2, 'title' => 'B', 'author_id' => 10],
['id' => 3, 'title' => 'C', 'author_id' => 20],
];
$authors = [10 => 'Ayşe', 20 => 'Deniz'];
$queryCount = 0;
foreach ($posts as $post) {
$queryCount++;
$author = $authors[$post['author_id']];
}
echo $queryCount . "\n";
$queryCount = 1;
echo $queryCount . "\n";
foreach ($posts as $post) {
echo $post['title'] . "\n";
}
Test yourself
-
Complete the eager loading call
$posts = fetchPosts();
$author_ids = array_column($posts, 'author_id');
$authors = ____('SELECT * FROM authors WHERE id IN (?)', $author_ids);
Show answer
$posts = fetchPosts();
$author_ids = array_column($posts, 'author_id');
$authors = fetchAll('SELECT * FROM authors WHERE id IN (?)', $author_ids);
-
Match the N+1 and eager loading concepts
- N+1
- A query per row
- eager loading
- Batch load in one query
- lazy loading
- Load on first access
- JOIN
- Merge relation in one query
-
When is eager loading preferred?
- When loading data for a single item
- When listing the relations of several rows
- When cleaning the cache
- When triggering the garbage collector
Show answer
When listing the relations of several rows — Correct. For list work eager loading avoids N+1 and drops the query count to two.
Next lesson: Lazy loading
