Query Performance Basics

A junior developer does not need to become a database optimiser, but they should recognise common performance problems and know the first checks to make.

Indexes Match Queries

An index helps the database find rows without scanning the whole table. Indexes should match the query pattern.

CREATE INDEX orders_status_created_at_idx
    ON orders (status, created_at);

This index supports a query like:

SELECT id, total_cents, created_at
FROM orders
WHERE status = :status
ORDER BY created_at DESC
LIMIT 20;

Do not add indexes to every column. Indexes take storage and slow down writes.

EXPLAIN

EXPLAIN asks the database how it plans to run a query.

EXPLAIN
SELECT id, total_cents
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

You are looking for clues: is the database using an index, scanning too many rows, sorting a huge result, or joining in a surprising order? The exact output differs between SQLite, MySQL, and PostgreSQL.

N+1 Queries

An N+1 query problem happens when code loads a list, then runs another query for each row.

<?php

declare(strict_types=1);

function nPlusOneQueryCount(int $orders): int
{
    return 1 + $orders;
}

echo nPlusOneQueryCount(50) . PHP_EOL;

// Prints:
// 51

For 50 orders, that is 1 query for the orders and 50 more queries for related users, items, or totals.

Fixes include joins, eager loading in an ORM, or loading related rows in one WHERE IN (...) query.

Batching

Batching processes data in chunks instead of loading everything at once.

<?php

declare(strict_types=1);

function batchesNeeded(int $rows, int $batchSize): int
{
    return (int) ceil($rows / $batchSize);
}

echo batchesNeeded(12000, 1000) . PHP_EOL;

// Prints:
// 12

Batching is common in imports, exports, backfills, and background jobs. It keeps memory and transaction size under control.

Pagination

Offset pagination is simple:

SELECT id, email
FROM users
ORDER BY id
LIMIT :limit OFFSET :offset;

It works well for many admin screens, but very high offsets can become slow because the database still has to walk past earlier rows.

Cursor pagination uses the last seen value:

SELECT id, email
FROM users
WHERE id > :last_seen_id
ORDER BY id
LIMIT :limit;

Cursor pagination is often better for large tables, exports, and APIs that page through many records.

What To Check

Before moving on, make sure you can:

• Add indexes for actual WHERE and ORDER BY patterns.
• Use EXPLAIN to inspect a query plan.
• Recognise an N+1 query pattern in PHP loops.
• Batch large jobs instead of loading everything.
• Choose offset or cursor pagination deliberately.
• Avoid guessing by checking real queries and data volumes.