Performance: N+1 Queries, Batching, Pagination, and Slow Queries
Recognize and fix the N+1 query problem with eager loading, apply batching for bulk operations, implement both offset and keyset pagination, use slow-query analysis to find real bottlenecks, and avoid selecting unnecessary columns.
A correct query can still be a slow application if it's run the wrong number of times, at the wrong moment, or pulls more data than needed. This lesson covers the most common, highest-impact database performance problem in web applications — the N+1 query pattern, where a loop silently issues one query per iteration instead of one query total — along with its fix (eager loading/batching), the two main pagination strategies and when to use each, how to find slow queries in production, and why SELECT * quietly costs more than it looks like.
The N+1 Query Problem
The N+1 problem happens when code runs one query to fetch a list of N records, and then, for each of those N records, runs another query to fetch related data — resulting in 1 + N total queries instead of 2 (or even 1). It's extremely easy to write by accident: a loop over a list of posts that calls a function to fetch each post's author inside the loop looks innocuous in isolation, but at 50 posts it's 51 round-trips to the database instead of 2, and every round-trip carries network latency on top of the query execution time itself.
The fix is eager loading (also called batch loading): instead of fetching related data one row at a time inside a loop, collect all the IDs you'll need up front, then run a single additional query using WHERE id IN (...) to fetch every related row at once, and match them back up in PHP using an associative array keyed by ID. This turns N+1 queries into exactly 2 queries, regardless of whether N is 10 or 10,000 — the win grows with the size of the list.
This pattern generalizes past a literal single-column relationship — the same fix applies any time a loop issues a query per iteration for related data: fetching each order's line items separately, each user's most recent login separately, each product's category name separately. The tell is always the same: a database call sitting inside a foreach.
• N+1: 1 query for a list of N rows, then N more queries (one per row) for related data. • Symptom: a database query call sitting inside a foreach loop. • Fix: collect IDs first, then a single WHERE id IN (...) query, matched back up in PHP. • Turns 1 + N queries into a constant 2 queries, regardless of how large N gets.
Batching Writes and Pagination
The same batching idea applies to writes, not just reads: inserting a thousand rows one at a time in a thousand separate INSERT statements is far slower than either a single multi-row INSERT INTO t (a, b) VALUES (?, ?), (?, ?), ... statement, or a smaller number of moderately-sized batches (e.g. 500 rows per batch) if the full set is too large for one statement. Batching amortizes the fixed per-query network/parsing overhead across many rows instead of paying it once per row.
Pagination limits how many rows a query returns at once, which matters both for response size and for how much work the database does per request. Offset pagination (LIMIT 20 OFFSET 40) is simple and lets a client jump to any page directly, but it has a real cost: the database still has to scan and discard all OFFSET rows before it can start returning the LIMIT rows, so a request for page 5,000 (OFFSET 100000) is measurably slower than page 1, even though both return the same number of rows.
Keyset pagination (also called cursor pagination) avoids that cost by remembering the last row's sort key from the previous page and filtering with a WHERE clause instead of an offset — e.g. WHERE id > :last_id ORDER BY id LIMIT 20. Because it's a WHERE condition an index can satisfy directly, performance stays roughly constant no matter how deep into the result set you page, at the cost of not being able to jump to an arbitrary page number directly (only 'next page from here'). Keyset pagination is the standard choice for infinite-scroll feeds and large, deep-paginated APIs.
• Batch writes: one multi-row INSERT (or a handful of moderate batches) instead of one INSERT per row. • Offset pagination (LIMIT/OFFSET): simple, supports jump-to-page, but gets slower the deeper you page. • Keyset pagination (WHERE id > :last_id ORDER BY id LIMIT n): roughly constant speed at any depth, but only supports 'next page', not arbitrary jumps. • Choose keyset pagination for deep/infinite-scroll feeds; offset pagination is fine for shallow, small result sets.
Finding Slow Queries
Guessing which query is slow is unreliable at any real scale — the standard approach is to turn on the database's slow query log (e.g. MySQL's slow_query_log with a long_query_time threshold) and let it record every query that exceeds that threshold in production, with real data and real load. Reviewing that log (or an equivalent APM/monitoring tool) regularly turns performance work from guesswork into a prioritized list: fix the queries that are both slow *and* run often first, since a query that's slow but runs once a day matters far less than one that's merely mediocre but runs on every page load.
Once a candidate slow query is identified, EXPLAIN (covered in the indexing lesson) is the next step — it tells you whether the query is missing an index, using the wrong one, or doing something structurally expensive like a full table scan or a filesort. Many ORMs and query builders also offer query logging in development that shows every query issued for a single request, which is often how an N+1 problem is first noticed: seeing fifty near-identical queries logged for one page load is the classic tell.
It's worth distinguishing a slow query from a query that's merely run too often — sometimes the fix isn't to make the query faster, it's to stop running it 50 times when 1 would do (which is exactly the N+1 fix), or to cache its result when the underlying data changes rarely.
• Enable the slow query log with a threshold (e.g. queries over 200ms) to capture real production offenders. • Prioritize by slow AND frequent, not just slowest — a rare slow query matters less than a mediocre common one. • Use EXPLAIN on flagged queries to see whether an index is missing or misused. • Per-request query logging (dev tools/ORM logs) is how N+1 patterns are usually first spotted — many near-identical queries for one page load.
SELECT * and Unnecessary Columns
SELECT * fetches every column in a table, including ones the application never uses for that particular query — large text/BLOB columns, columns added later for unrelated features, columns only needed on a different page. This costs more network bandwidth transferring the data, more PHP memory hydrating it into objects/arrays, and can silently defeat a covering index (as covered in the indexing lesson) that would otherwise have let the database answer the query from the index alone without a row lookup.
Explicitly listing only the columns a query actually needs (SELECT id, email, created_at FROM users instead of SELECT * FROM users) is a small, easy habit with a real payoff, especially on tables with large or many columns, or in hot code paths run on every request. It also makes the code more self-documenting — a reader can see exactly what data a piece of code depends on without having to check the schema.
This matters even more in the N+1 fix's batch-loading query: fetching every column for every related row in a WHERE id IN (...) batch multiplies the unnecessary-column cost by however many rows the batch contains, so tightening the column list is one of the easiest wins to combine with eager loading.
• SELECT * pulls every column, even ones the current query doesn't use. • Costs: extra network transfer, extra PHP memory to hydrate, can defeat a covering index. • List only the needed columns explicitly, especially in hot paths and batch-loaded queries. • Explicit column lists are also more self-documenting about what data the code actually depends on.
Keyset Pagination Query Shape
The column used as the cursor (id here) should be indexed and have a stable, unique sort order. Compare with offset pagination's LIMIT 20 OFFSET 40, which gets slower as OFFSET grows. For multi-column sorts, the WHERE clause needs a tuple comparison, e.g. WHERE (created_at, id) > (:last_created_at, :last_id).
-- First page
SELECT id, title, created_at FROM posts
ORDER BY id
LIMIT 20;
-- Next page: pass the last row's id from the previous page as the cursor
SELECT id, title, created_at FROM posts
WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;Code Examples
The query to fetch an author's name is issued fresh on every loop iteration. For 50 posts, this is 51 separate round-trips to the database — each one paying full network and parsing overhead — when the same information could be retrieved in a small constant number of queries.
foreach ($posts as $post) { $stmt = $pdo->prepare('SELECT name FROM users WHERE id = :id'); ... }
Common mistake: Any database call inside a foreach over rows fetched from an earlier query is almost always an N+1 bug waiting to be noticed at scale.
Instead of querying inside the loop, the author IDs are collected first, and a single WHERE id IN (...) query fetches every needed author row at once. The results are indexed into an associative array by ID so the render loop can look each one up in constant time with no further database calls — reducing 51 queries to exactly 2. Expected result: Same output as the N+1 version, produced with 2 queries instead of 51 — the gap widens further as the post count grows.
$stmt = $pdo->prepare("SELECT id, name FROM users WHERE id IN ($placeholders)");
getPageByOffset recomputes OFFSET from the page number and lets the database skip that many rows — simple to call by page number, but the database still has to walk through every skipped row internally. getPageByKeyset instead filters with WHERE id > :last, which an index on id can satisfy directly by jumping straight to the right spot, so performance doesn't degrade as the cursor moves deeper into the table. Keyset pagination requires the client to pass along the last row's id (or a composite cursor) between requests instead of just a page number.
WHERE id >:last ORDER BY id LIMIT:limit The first query transfers and hydrates every column in the posts table for 20 rows, even though the loop only ever reads title. The second query asks for exactly the three columns actually used, cutting network transfer and PHP memory usage, and potentially letting a covering index answer the query without a separate row lookup.
SELECT id, title, published_at FROM posts LIMIT 20 Common mistake: SELECT * is convenient while prototyping but should almost always be replaced with an explicit column list before code reaches production, especially in hot paths and loops.
<?php
// SLOW: 1 query for posts, then 1 more query PER post for its author.
$posts = $pdo->query('SELECT id, title, author_id FROM posts LIMIT 50')
->fetchAll(PDO::FETCH_ASSOC);
foreach ($posts as $post) {
$stmt = $pdo->prepare('SELECT name FROM users WHERE id = :id');
$stmt->execute(['id' => $post['author_id']]);
$author = $stmt->fetch(PDO::FETCH_ASSOC);
echo $post['title'] . ' by ' . $author['name'] . PHP_EOL;
}
// Total: 1 + 50 = 51 database round-trips for 50 posts.<?php
declare(strict_types=1);
$posts = $pdo->query('SELECT id, title, author_id FROM posts LIMIT 50')
->fetchAll(PDO::FETCH_ASSOC);
// Collect the distinct author IDs we actually need.
$authorIds = array_values(array_unique(array_column($posts, 'author_id')));
// One query for ALL needed authors, regardless of how many posts there are.
$placeholders = implode(',', array_fill(0, count($authorIds), '?'));
$stmt = $pdo->prepare("SELECT id, name FROM users WHERE id IN ($placeholders)");
$stmt->execute($authorIds);
// Index authors by id for O(1) lookup while rendering.
$authorsById = [];
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $author) {
$authorsById[$author['id']] = $author;
}
foreach ($posts as $post) {
$author = $authorsById[$post['author_id']];
echo $post['title'] . ' by ' . $author['name'] . PHP_EOL;
}
// Total: 2 database round-trips, no matter how many posts there are.<?php
declare(strict_types=1);
// Offset pagination — simple, but gets slower on deep pages
// (the DB must scan and discard $offset rows before returning any).
function getPageByOffset(PDO $pdo, int $page, int $perPage = 20): array
{
$offset = ($page - 1) * $perPage;
$stmt = $pdo->prepare('SELECT id, title FROM posts ORDER BY id LIMIT :limit OFFSET :offset');
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
// Keyset pagination — roughly constant speed at any depth via an indexed WHERE.
function getPageByKeyset(PDO $pdo, ?int $lastSeenId, int $perPage = 20): array
{
if ($lastSeenId === null) {
$stmt = $pdo->prepare('SELECT id, title FROM posts ORDER BY id LIMIT :limit');
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
} else {
$stmt = $pdo->prepare('SELECT id, title FROM posts WHERE id > :last ORDER BY id LIMIT :limit');
$stmt->bindValue(':last', $lastSeenId, PDO::PARAM_INT);
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
}
$stmt->execute();
return $stmt->fetchAll(
…<?php
// Wasteful: pulls every column, including large/unused ones like 'body_html'
// and 'raw_metadata', even though only 3 fields are actually rendered.
$stmt = $pdo->query('SELECT * FROM posts LIMIT 20');
// Better: only the columns this code path actually uses.
$stmt = $pdo->query('SELECT id, title, published_at FROM posts LIMIT 20');
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $post) {
echo $post['title'] . PHP_EOL;
}N+1 vs. Eager Loading
Eager loading turns a query count that scales with N into a constant number of queries.
N+1 (bad): Eager loading (good):
SELECT * FROM posts LIMIT 50 SELECT * FROM posts LIMIT 50
for each post (x50): collect all author_ids
SELECT * FROM users SELECT * FROM users
WHERE id = post.author_id WHERE id IN (...) <- 1 query
match back up in PHP
= 51 total queries = 2 total queriesImportant Rules
Never issue a database query inside a foreach loop over rows from an earlier query — collect IDs and batch-load instead.
Batch bulk writes into multi-row INSERTs or moderate-sized chunks instead of one statement per row.
Use offset pagination only for shallow/small result sets; use keyset pagination for deep or infinite-scroll pagination.
Enable and regularly review the slow query log; prioritize fixes by slow-and-frequent, not just slowest.
Select only the columns a query actually uses — avoid SELECT * in application code, especially in hot paths.
Why This Is the First Thing Profilers Find
N+1 queries are consistently the single most common performance bug found when profiling real-world web applications, because they're invisible in code review (a single, innocent-looking function call inside a loop) and invisible in manual testing with a handful of rows, but scale linearly with data size — a page that feels instant with 10 test rows can issue hundreds of queries and take seconds once real data (10,000 rows) is loaded. Combined with unnecessary SELECT * columns and offset pagination on large tables, these three habits account for a large share of 'it got slow in production' incidents that trace back not to a broken query, but to a correct query run the wrong number of times, at the wrong depth, pulling more data than needed.
foreach ($orders as $order) { $items = $pdo->query("SELECT * FROM order_items WHERE order_id = {$order['id']}")->fetchAll(); }
Collect all order IDs, run one SELECT * FROM order_items WHERE order_id IN (...), then group results back by order_id in PHP.
This is both an N+1 problem and an unprepared, concatenated query (a SQL injection risk) at once — the fix batches the query into one round-trip and should also use bound parameters for the IN list.
Using LIMIT 20 OFFSET 200000 to serve page 10,000 of an infinite-scroll feed.
Use keyset pagination: WHERE id > :last_seen_id ORDER BY id LIMIT 20.
A large OFFSET forces the database to scan and discard 200,000 rows before returning any — this gets progressively slower the deeper a user scrolls, whereas keyset pagination stays roughly constant-time via an indexed WHERE clause.
What is the defining symptom of an N+1 query problem? A. A single query that takes an unusually long time to run. B. A loop over N rows from one query that issues an additional database query on each iteration, resulting in 1 + N total queries. C. A query that returns N+1 rows instead of the expected N rows. D. A transaction that requires N+1 commits to complete.
B. A loop over N rows from one query that issues an additional database query on each iteration, resulting in 1 + N total queries. — N+1 refers to the query count: 1 query fetches the initial list, then N more queries (one per row) fetch related data inside a loop — the fix is to replace those N queries with a single batched query.
Why does offset pagination (LIMIT/OFFSET) get slower on deep pages, while keyset pagination does not? A. Offset pagination requires a full table lock on every request. B. The database must scan and discard every row up to OFFSET before returning results, whereas keyset pagination filters via an indexed WHERE clause that jumps directly to the right spot. C. Keyset pagination caches every previous page automatically. D. Offset pagination is not supported by any SQL database past a certain page size.
B. The database must scan and discard every row up to OFFSET before returning results, whereas keyset pagination filters via an indexed WHERE clause that jumps directly to the right spot. — OFFSET forces the engine to walk through and discard that many rows before it can start returning the LIMIT rows, so cost grows with page depth. Keyset pagination instead uses an indexed comparison (id > last_seen_id), which stays roughly constant-time regardless of depth.
Why can SELECT * sometimes prevent a covering index from working as intended? A. SELECT * always disables indexes entirely for the whole query. B. A covering index only contains the specific columns it was designed for; if the query selects extra columns not in that index, the database must go back to the full table row, defeating the point of the covering index. C. SELECT * automatically forces a full table scan regardless of any index. D. SELECT * is not valid SQL syntax and must be rewritten before the query can run at all.
B. A covering index only contains the specific columns it was designed for; if the query selects extra columns not in that index, the database must go back to the full table row, defeating the point of the covering index. — A covering index answers a query entirely from the index only if every selected (and filtered) column is present in it. Selecting unnecessary extra columns with SELECT * often forces a lookup back to the table row, losing the covering-index benefit.
Rewrite this N+1 code to use eager loading: foreach ($comments as $comment) { $stmt = $pdo->prepare('SELECT username FROM users WHERE id = ?'); $stmt->execute([$comment['user_id']]); $user = $stmt->fetch(); echo $user['username']; }
$userIds = array_values(array_unique(array_column($comments, 'user_id'))); $ph = implode(',', array_fill(0, count($userIds), '?')); $stmt = $pdo->prepare("SELECT id, username FROM users WHERE id IN ($ph)"); $stmt->execute($userIds); $usersById = []; foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $u) { $usersById[$u['id']] = $u; } foreach ($comments as $comment) { echo $usersById[$comment['user_id']]['username']; }
Collect distinct user_id values from $comments first, run one WHERE id IN (...) query, index the results by id, then loop over $comments again doing only array lookups.