Query Performance

Performance work begins with measurement. Capture the query, parameters, data volume, execution plan, frequency, and user-facing latency before changing code or adding indexes.

Measure The Complete Request

A slow page may spend time in PHP, network calls, templates, or the database. Database instrumentation should record query duration and count without logging secrets or personal values.

For a representative request, ask:

• How many SQL statements execute?
• Which statements dominate total time?
• How many rows are examined and returned?
• How often does the request run?
• Are connections waiting or locks blocking?
• Is time spent transferring unused columns?
• Does the problem occur only with production-scale data?

One 300-millisecond query and 300 one-millisecond queries require different fixes.

Indexes Support Access Patterns

An index is an ordered structure that helps a database find rows without scanning every table row. It also consumes storage and adds work to inserts, updates, and deletes.

Start from an actual query:

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

A matching composite index could be:

CREATE INDEX orders_status_created_id_idx
    ON orders (status, created_at DESC, id DESC);

The index begins with the equality filter, then follows the requested order. Exact optimizer behavior and descending-index support depend on the database.

Composite Column Order Matters

An index on (status, created_at) is not interchangeable with (created_at, status). Ordered indexes are commonly most useful from their leading columns.

The first index can support WHERE status = ? and often WHERE status = ? ORDER BY created_at. It may not efficiently support a query filtering only by created_at in the same way.

Choose order from real predicates:

• equality columns often come before range columns;
• ordering columns can follow usable filters;
• highly selective predicates can reduce work;
• joins need indexes on relevant keys;
• every extra column increases index size and write cost.

Do not apply one simplistic rule to every database plan. Confirm with EXPLAIN and representative values.

Selectivity Changes Usefulness

An index on a boolean column may not help when half the table has each value. Scanning the table can be cheaper than following many index entries and fetching rows.

A composite index such as (is_active, last_seen_at) may help a query that filters active users and orders a small recent subset. Partial or filtered indexes, where supported, can index only rows matching a condition.

Data distribution matters. A status that is rare in development but common in production can produce a different plan. Statistics must be current enough for the optimizer to estimate accurately.

Avoid Indexing Every Column

Each index must be maintained during writes. Excessive indexes increase storage, cache pressure, migration time, replication work, and lock duration. Overlapping indexes may provide little additional value.

Before adding one, inspect existing indexes, query frequency, selectivity, and expected write cost. After deployment, verify that the target plan uses it and remove proven-unused indexes through a reviewed process.

An index added for one query can affect others. Performance changes require observation after release.

Read EXPLAIN As Evidence

EXPLAIN asks the database how it plans to execute a statement:

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

Output differs across SQLite, MySQL/MariaDB, and PostgreSQL. Look for:

• table or index scans;
• estimated rows;
• actual rows where execution analysis is enabled;
• selected indexes;
• join algorithms and order;
• filters applied late;
• explicit sorts or temporary structures;
• repeated loops over inner operations.

A sequential scan is not automatically bad. Reading most of a small table can be faster than index lookups. The question is whether the plan matches data size and query purpose.

Some EXPLAIN ANALYZE forms execute the statement. Be especially careful with writes and production traffic. Use database-specific documentation and a safe environment.

Estimated Versus Actual Rows

Large differences between estimated and actual row counts can lead to poor join or scan choices. Causes include stale statistics, correlated columns, skewed values, expressions, and parameters unlike the optimizer's assumptions.

Do not immediately force an index. Refresh statistics where appropriate, improve predicates, use engine-supported extended statistics, or redesign the query. Hints create maintenance obligations and should be a deliberate last option.

Capture plans with representative parameter values. A query for a common status may need a different strategy from a rare status.

Select Only Required Data

SELECT * transfers columns the page may never use, prevents some covering-index opportunities, and couples code to schema changes.

SELECT id, order_number, total_pence
FROM orders
WHERE customer_id = :customer_id
ORDER BY id DESC
LIMIT 20;

Large text, JSON, and binary columns are particularly expensive. Load detail fields only on the detail path.

Returning fewer rows is usually more important than micro-optimizing PHP iteration. Apply filters and limits in SQL when they represent database predicates.

Recognize N+1 Queries

N+1 occurs when code loads a collection and then queries related data once for every item:

<?php

declare(strict_types=1);

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

echo queryCountForOrders(50), PHP_EOL;

// Prints:
// 51

The problem grows with result size and network latency. It often hides behind ORM property access, repository calls inside loops, template helpers, or serialization.

Detect it through query logs, framework profilers, integration tests with query-count assertions, and tracing. Do not rely only on local speed with five rows.

Replace N+1 Deliberately

Possible fixes include:

• join required data in one query;
• eager-load ORM relationships;
• load related rows with one WHERE IN (...) query;
• aggregate in SQL;
• use a dedicated read model;
• batch through a data-loader layer.

A large join can duplicate parent columns for every child and create a huge result. Two bounded queries can be clearer: load 20 orders, then load all lines whose order_id belongs to those 20 IDs and group them in PHP.

<?php

declare(strict_types=1);

function groupRowsByOrder(array $rows): array
{
    $grouped = [];

    foreach ($rows as $row) {
        $grouped[$row['order_id']][] = $row;
    }

    return $grouped;
}

Choose based on result shape and measured cost, not a rule that every relationship must use one query.

Batch Large Jobs

Imports, exports, cleanup, and backfills should process bounded chunks. Loading all rows with fetchAll() can exhaust memory and create an enormous transaction.

Use stable keyset batches:

SELECT id, email
FROM users
WHERE id > :last_id
ORDER BY id
LIMIT :batch_size;

After processing, set last_id to the greatest ID in the batch. This avoids increasingly expensive offsets and remains stable when earlier rows are deleted.

Choose a batch size through measurement. Too small creates excessive round trips and commits. Too large increases memory, lock duration, retry cost, and queue time.

Make Batch Work Restartable

A robust job records progress and makes each item or batch idempotent. If a worker stops after batch 42, it should resume without repeating harmful side effects.

Store a checkpoint only after the batch's durable work commits. If the job writes to external systems, use stable operation IDs. Log counts of processed, skipped, rejected, and failed records.

Rows can change while a long job runs. Decide whether the job processes a fixed snapshot, everything up to a captured maximum ID, or continually follows new rows. State that contract explicitly.

Offset Pagination

Offset pagination is easy for numbered pages:

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

High offsets can become expensive because the database still locates and discards preceding rows. Concurrent inserts and deletes can also cause duplicates or omissions between page requests.

Offset remains reasonable for small administrative datasets, shallow pages, and interfaces that require jumping to page numbers. Measure before replacing it.

Keyset Pagination

Keyset pagination continues after the last ordered values:

SELECT id, created_at, total_pence
FROM orders
WHERE (created_at, id) < (:created_at, :id)
ORDER BY created_at DESC, id DESC
LIMIT :limit;

The unique id tie-breaker creates deterministic order when several rows share a timestamp. Syntax for row-value comparisons varies; equivalent boolean predicates can be used.

A cursor should encode all ordering values and be validated when decoded. It is an opaque continuation token, not trusted SQL.

Keyset pagination performs well for sequential browsing but does not naturally jump to arbitrary page numbers. It also needs a stable ordering and matching index.

Count Queries Have A Cost

Displaying "page 1 of 87,492" may require a full or large count. On high-volume systems, exact counts can be expensive or quickly stale.

Alternatives include showing whether another page exists by fetching one extra row, using an approximate count, caching a summary, or calculating exact counts only on demand.

Do not run an expensive count automatically if the user does not need it.

Joins Need Indexed Relationships

Foreign keys express integrity but do not guarantee every referencing column is indexed in every database. Join and delete behavior can suffer without appropriate indexes.

For:

SELECT ol.order_id, ol.quantity
FROM order_lines AS ol
WHERE ol.order_id = :order_id;

an index on order_lines(order_id) is normally important. Composite indexes may include further filters or ordering columns.

Inspect both sides of frequent joins and verify plans. Avoid wrapping indexed columns in functions that prevent ordinary index use unless the database has a matching expression index.

Queries Can Be Correct And Still Unsafe At Scale

An unbounded export, wildcard search beginning with %, huge IN list, or sort on an unindexed expression may work in tests and overload production.

Set limits, timeouts, request validation, and background-job boundaries. Expensive reporting may belong on a replica or analytics store, but replica lag and workload isolation must be understood.

Do not solve every slow query with caching. First fix unnecessary work and indexing. Caching introduces invalidation and stale-data behavior.

Establish Query Budgets

Teams can set practical budgets for critical paths:

Product list: at most 5 queries, database time under 80 ms at p95
Checkout write: at most 8 statements, no query over 50 ms under normal load
Worker batch: 500 rows, peak memory under 128 MB

Budgets turn vague performance concern into testable expectations. Monitor percentiles rather than only averages, and review budgets as traffic and data grow.

Verify Improvements

For each change:

1. Capture the original query, plan, timing, and row counts.
2. Apply one understandable change.
3. Re-run with representative data and parameters.
4. Check write and storage cost when adding indexes.
5. Test correctness and pagination boundaries.
6. Observe production metrics after deployment.
7. Record the result and rollback path.

A faster query returning wrong or incomplete data is not an improvement.

Review Checklist

When investigating database performance, verify:

• query count and total database time;
• representative parameters and data volume;
• selected columns and result limits;
• indexes aligned with filters, joins, and ordering;
• actual execution plans;
• N+1 behavior in loops and serializers;
• bounded, restartable batch processing;
• deliberate offset or keyset pagination;
• cost of counts and exports;
• lock waits and transaction duration;
• correctness before and after optimization.

What You Should Be Able To Do

After this lesson, you should be able to design a composite index from an access pattern, read the major signals in an execution plan, identify N+1 behavior, and select a suitable join, eager load, or batched lookup.

You should also be able to implement restartable keyset batches, choose pagination according to product requirements, define a query budget, and prove an optimization with measurements rather than assumptions.

For the growth model behind repeated scans and lookups, continue with Complexity And Growth and PHP Arrays, Maps, And Sets.