Common SQL Performance Problems I Have Encountered

Starter draft. The patterns below are common ones; swap in examples from systems you have worked on.

Almost every slow page I have investigated came down to one of a handful of patterns. None of them are exotic.

1. The N+1 query#

The classic. One query to fetch a list, then one more per row.

$orders = Order::where('status', 'placed')->get();

foreach ($orders as $order) {
    echo $order->customer->name;   // a query, per order
}

Fifty orders, fifty-one queries. Each is fast; the round trips are what cost you.

$orders = Order::with('customer')->where('status', 'placed')->get();

Every ORM has an equivalent. The difficulty is noticing: this is invisible in code review and obvious the moment you log query counts per request.

2. A predicate that cannot use an index#

An index on created_at is useless if the query wraps the column in a function.

-- Index cannot be used: the column is inside a function
WHERE YEAR(created_at) = 2026

-- Index can be used
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

The same applies to leading wildcards (LIKE '%term'), implicit type conversions (comparing a VARCHAR column to a number), and functions applied to a column for case-insensitive matching.

Rule of thumb: keep the column bare on one side of the comparison.

3. SELECT * on a wide table#

Selecting every column forces the engine past covering indexes and drags text and blob columns across the network for no reason.

-- Reads the whole row, every time
SELECT * FROM orders WHERE status = 'placed';

-- Can be satisfied by a covering index
SELECT id, customer_id, total, created_at FROM orders WHERE status = 'placed';

4. Missing indexes on foreign keys and filter columns#

Most engines index primary keys automatically and foreign keys not at all. Every join column, and every column that regularly appears in a WHERE, deserves consideration.

Composite index order matters: an index on (status, created_at) serves a query filtering on status and sorting by created_at. The reverse order does not serve it nearly as well. Leftmost columns first.

5. Pagination with a large OFFSET#

-- The engine still walks the first 100,000 rows to discard them
SELECT * FROM orders ORDER BY created_at DESC LIMIT 25 OFFSET 100000;

Keyset pagination stays flat regardless of depth:

SELECT * FROM orders
WHERE created_at < '2026-02-01 09:14:22'
ORDER BY created_at DESC
LIMIT 25;

It is a larger change to the API — a cursor rather than a page number — which is why it is worth deciding before the table is large.

6. Work inside the request that does not need to be#

Report generation, large exports and bulk recalculation inside an HTTP request produce timeouts, and then retries, which produce more load. Move them out of the request path.

7. Trusting a small dataset#

Every one of these is invisible with a thousand rows and painful with a million. Test against production-shaped volumes, or you are testing something other than what you will ship.

How to actually find the problem#

Guessing is slower than measuring, every time.

1. Read the execution plan. EXPLAIN in MySQL and PostgreSQL, the actual execution plan in SQL Server. Look for full scans on large tables, and for an estimated row count wildly different from the actual one — that usually means stale statistics.
2. Log slow queries. MySQL's slow query log, pg_stat_statements, SQL Server's Query Store. The worst query is rarely the one anyone suspected.
3. Count queries per request. N+1 problems are obvious the moment you can see the number.
4. Measure before and after. "It feels faster" is not a result.

What I would check first#

• [ ] How many queries does this request run?
• [ ] Is the execution plan scanning where it should seek?
• [ ] Do all join and filter columns have usable indexes?
• [ ] Is any indexed column wrapped in a function?
• [ ] Are we selecting columns we do not use?
• [ ] Does this query slow down as the table grows?