Common Slow Patterns
N+1 queries, deep OFFSET paging, SELECT * and unbounded IN lists: four recurring slow patterns and the rewrite for each.
WHERE (sort_col, id) < (:last_sort, :last_id) ORDER BY sort_col DESC, id DESC LIMIT n -- keyset pagingMost slow endpoints are not slow because of an exotic query. They are slow because of one of a small set of patterns that look harmless at development data volumes and fall over at production volumes.
N+1
One query returns a list, then the application issues one query per row. Every one of those may be indexed and fast, and the endpoint is still slow because of the round trips. Replace it with one join, or one IN query per level, and group in the application.
Deep OFFSET
LIMIT 20 OFFSET 100000 asks the server to produce 100,020 rows and throw 100,000 away. Cost grows with the page number. The fix is keyset (seek) paging: remember the last row's sort key and turn the page boundary into an index range, which costs the same at page 1 and page 5000.
SELECT *
It guarantees no covering index can apply, drags TEXT and BLOB payloads across the wire for rows the caller discards, and quietly changes shape when someone adds a column. Name the columns you render.
Unbounded IN lists
An IN list built from an array grows with the caller's input. The range optimizer has a memory budget, range_optimizer_max_mem_size (0 means no limit); when a query would exceed it, MySQL abandons range access, warns, and may fall back to a full table scan. Two IN lists are worse: M values and N values make M × N predicates.
Example
-- KEY idx_cust_placed (customer_id, placed_at, id) on orders
-- Slow: the server builds 100,020 rows and discards 100,000 of them.
SELECT id, placed_at, total_cents FROM orders
WHERE customer_id = 42
ORDER BY placed_at DESC, id DESC
LIMIT 20 OFFSET 100000;
-- Keyset paging, row-constructor shorthand. Carry the last row of the
-- previous page forward as the boundary.
SELECT id, placed_at, total_cents FROM orders
WHERE customer_id = 42
AND (placed_at, id) < ('2026-03-14 09:12:00', 884213)
ORDER BY placed_at DESC, id DESC
LIMIT 20;
-- The longhand it expands to. Check key_len in EXPLAIN and prefer whichever
-- form uses more key parts: the manual warns that mixing a row constructor
-- with AND can leave the optimizer using only the leading column.
SELECT id, placed_at, total_cents FROM orders
WHERE customer_id = 42
AND (placed_at < '2026-03-14 09:12:00'
OR (placed_at = '2026-03-14 09:12:00' AND id < 884213))
ORDER BY placed_at DESC, id DESC
LIMIT 20;
-- id is the tiebreaker that makes the order total. Without it, two orders
-- sharing a placed_at value can be skipped or repeated across pages.When to use it
- Fixing an endpoint whose log shows hundreds of near-identical queries per request.
- Making page 500 of a listing as fast as page 1.
- Handling a bulk lookup endpoint that accepts an arbitrary array of ids.
- Cutting response size and query time on a list view without adding an index.
More examples
N+1 collapsed into one round trip
The tell is a log full of identical statements differing only in a parameter. Each is fast; the total is not, because you pay network latency and parse cost per row. Fetch the children for the whole parent set in one statement, ordered by the parent key so the application can group them in a single pass. The same reasoning applies to per-row counts: one GROUP BY beats N COUNT(*) queries.
-- N+1: one query for the list, then one per row. 200 parents = 201 queries.
SELECT id FROM orders WHERE customer_id = 42;
-- then, in a loop:
SELECT id, sku, qty FROM order_items WHERE order_id = 9001;
SELECT id, sku, qty FROM order_items WHERE order_id = 9002;
-- ...
-- One round trip. Needs KEY (order_id) on order_items, which the foreign
-- key wants anyway. Sorting by order_id lets the app group in one pass.
SELECT oi.order_id, oi.id, oi.sku, oi.qty
FROM order_items AS oi
JOIN orders AS o ON o.id = oi.order_id
WHERE o.customer_id = 42
ORDER BY oi.order_id, oi.id;
-- Per-row aggregates are the same mistake in another shape. Not this:
-- for each order: SELECT COUNT(*) FROM order_items WHERE order_id = ?
SELECT o.id, COUNT(oi.id) AS item_count, COALESCE(SUM(oi.qty), 0) AS units
FROM orders AS o
LEFT JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.customer_id = 42
GROUP BY o.id;
-- Find the pattern in production: high COUNT_STAR, tiny rows per execution.
SELECT DIGEST_TEXT, COUNT_STAR,
SUM_ROWS_SENT / COUNT_STAR AS rows_per_exec
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE() AND COUNT_STAR > 1000
ORDER BY COUNT_STAR DESC
LIMIT 10;Unbounded IN lists
An IN list whose length comes from user input is a latent full table scan. Past the range optimizer's memory budget MySQL gives up on range access entirely and warns with error 3170, and you get a scan on a table where the index was fine. Chunk the list in the application, or materialize the ids into a temporary table and join — which also gives the optimizer real cardinality to work with.
-- An id list straight from a request body.
SELECT id, name, price_cents FROM products
WHERE id IN (1, 2, 3 /* ...and 200,000 more... */);
SHOW WARNINGS;
-- Warning 3170 Memory capacity of N bytes for 'range_optimizer_max_mem_size'
-- exceeded. Range optimization was not done for this query.
-- Range access is abandoned; a full table scan may be chosen instead.
-- (With sql_safe_updates on, an UPDATE or DELETE in this state errors out.)
SHOW VARIABLES LIKE 'range_optimizer_max_mem_size'; -- 0 means no limit
-- Fix 1: chunk in the application, a few thousand ids per statement.
-- Fix 2: materialize the ids and join. The join gets an eq_ref plan and the
-- optimizer sees a real row count.
CREATE TEMPORARY TABLE wanted_ids (
id BIGINT UNSIGNED NOT NULL PRIMARY KEY
) ENGINE=InnoDB;
-- bulk-load wanted_ids with a multi-row INSERT or LOAD DATA, then:
SELECT p.id, p.name, p.price_cents
FROM products AS p
JOIN wanted_ids AS w ON w.id = p.id;
-- Two IN lists multiply: 500 values AND 500 values is 250,000 predicates.
SELECT COUNT(*) FROM orders
WHERE customer_id IN (/* 500 ids */) AND status IN (/* 500 values */);Trimming the row, not just the plan
Once the index is right, the remaining cost is the bytes. SELECT * blocks covering indexes and ships TEXT and JSON payloads for rows the caller never renders; a sort over wide rows is also more likely to spill. Naming columns is the cheapest change in this lesson and the one most often skipped. Verify with EXPLAIN ANALYZE rather than assuming.
-- orders has a TEXT column. This can never be covered, and the sort carries
-- the full row.
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY placed_at DESC
LIMIT 20;
-- Extra: Using index condition (rows are read from the table)
-- Named columns, all present in KEY (customer_id, placed_at, total_cents).
SELECT id, placed_at, total_cents FROM orders
WHERE customer_id = 42
ORDER BY placed_at DESC
LIMIT 20;
-- Extra: Backward index scan; Using index (table never touched)
-- When the caller genuinely needs wide rows deep in a result set, cover the
-- key lookup and fetch the rows by primary key afterwards.
SELECT o.*
FROM orders AS o
JOIN (
SELECT id FROM orders
WHERE customer_id = 42
ORDER BY placed_at DESC
LIMIT 20 OFFSET 10000
) AS page ON page.id = o.id
ORDER BY o.placed_at DESC;
-- Measure, do not assume.
EXPLAIN ANALYZE SELECT id, placed_at, total_cents FROM orders
WHERE customer_id = 42 ORDER BY placed_at DESC LIMIT 20;
Discussion