Database performance problems rarely announce themselves politely. They arrive as a dashboard that loads a little slower, a queue that begins to lag, or an API endpoint whose response time drifts upward under ordinary traffic. By the time users report it, the expensive query has often been running successfully for weeks.

The good news is that most query bottlenecks are discoverable long before they become incidents. The habit to build is simple: treat a query as an execution plan, not merely as a string that returns correct results.

Start with the question behind the query

A slow query is not automatically a database problem. It may be asked to do too much, be called too often, or return far more data than its caller needs. Before reaching for an index, clarify the intended unit of work.

Consider an endpoint that lists recent orders for a customer. Its real requirements might be: show 25 records, sorted newest first, with a small summary of each order. That is very different from fetching every order, joining every related table, and allowing application code to trim the result afterward.

Make these questions routine during review:

• How many rows should this operation reasonably examine?
• How many rows and columns does the caller actually need?
• How often will this query run per request, job, or batch?
• Does pagination remain efficient on later pages?
• Can the work be split into a summary query and an on-demand detail query?

Correctness is the first requirement. Predictable cost is the next one.

Read the execution plan before guessing

Most relational databases provide an EXPLAIN facility that reveals how the optimizer intends to execute a statement. The exact output differs by database engine, but the useful concepts are consistent: which tables are read, which indexes are considered or selected, join order, estimated row counts, sorting, and temporary work.

EXPLAIN
SELECT id, status, created_at
FROM orders
WHERE customer_id = 42
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 25;

Do not reduce plan reading to one rule such as “a full table scan is always bad.” A scan over a small lookup table can be entirely sensible. The warning sign is a mismatch between the work the database performs and the work the application needs. If a request needs 25 rows but the plan must inspect a very large portion of a growing table, that deserves attention.

Estimates matter too. An optimizer makes choices using table statistics and assumptions about data distribution. When estimated rows are dramatically different from reality, an otherwise reasonable plan can become a poor one. That is a reason to inspect database-specific statistics and maintenance practices, rather than forcing a plan based on a single production symptom.

Design indexes around access patterns

An index is useful when it supports a recurring way the system finds or orders data. It is not a decorative checklist item to add to every column.

For the order query above, a composite index beginning with customer_id and then created_at may align with the filter and requested order:

CREATE INDEX idx_orders_customer_created_at
ON orders (customer_id, created_at);

The order of columns is deliberate. The query first narrows results to a customer and then needs those rows in time order. A different workload may need a different index. For example, an operations screen that filters by status across all customers has a separate access pattern; adding it blindly to the index above may make neither workload ideal.

Every index also has a cost. Inserts, updates, deletes, storage, backups, and schema changes all become more expensive as indexes accumulate. Indexes should be justified by observed query patterns, verified against an execution plan, and revisited as the application evolves.

Watch for predicates that hide indexed columns

A common source of accidental inefficiency is applying a function or transformation to the indexed column in the predicate. This can make it harder for the database to use a straightforward range lookup.

SELECT id, created_at
FROM orders
WHERE DATE(created_at) = '2026-03-15';

When the intention is a calendar-day range, expressing it as a range is often clearer and more index-friendly:

SELECT id, created_at
FROM orders
WHERE created_at >= '2026-03-15'
  AND created_at < '2026-03-16';

The same principle applies to unplanned type conversions, leading wildcard searches such as LIKE '%term', and expressions that require the engine to transform every candidate row before comparing it.

Find the N+1 query before optimizing a single statement

One fast query can still be part of a slow request. The classic N+1 pattern happens when code loads a collection and then executes another query for every item in that collection.

$orders = $orderRepository->recentForCustomer($customerId);

foreach ($orders as $order) {
    $order->items = $itemRepository->forOrder($order->id);
}

If the list contains 25 orders, this creates at least 26 database round trips. On a developer machine it may appear harmless; under concurrent load, connection use and network latency compound quickly.

The remedy is not always one enormous join. Depending on the data shape, a bulk query using the order IDs, eager loading in an ORM, or a purpose-built read model may be cleaner. The key is to make query count visible in development and tests, then inspect the complete request path rather than celebrating the speed of one statement.

Pagination is a performance contract

Offset pagination is easy to write:

SELECT id, created_at, status
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 25 OFFSET 5000;

But deep offsets can require the database to walk past many rows before returning the requested page. For feeds and large histories, keyset pagination is often a better fit. It uses the last observed sort value, plus a stable tie-breaker when needed, to continue from a known position.

SELECT id, created_at, status
FROM orders
WHERE customer_id = 42
  AND created_at < '2026-03-15 10:30:00'
ORDER BY created_at DESC
LIMIT 25;

The precise cursor condition must match the ordering and account for duplicate sort values. That detail is important: pagination bugs often look like missing or duplicated records, not obvious database failures.

Measure in conditions that resemble reality

A query plan is evidence, but it is not the entire system. Test representative data volumes, realistic value distributions, and the request paths that invoke the query. Include the cost of serialization, network transfer, connection pooling, transaction scope, and related queries.

In PHP services, also avoid treating the database layer as invisible. Log slow operations with enough context to identify the endpoint and query shape, while avoiding sensitive values. Use parameterized queries for safety and plan stability; never construct SQL by concatenating user input. Set sensible timeouts so one pathological query does not indefinitely occupy application workers and database connections.

When a slow query appears, preserve the facts: the normalized SQL shape, parameters or parameter characteristics handled safely, execution plan, observed duration, row counts, and concurrency conditions. That record turns tuning from folklore into an engineering decision.

Make query review part of normal design

The best database optimizations happen before urgency narrows the conversation. Review the likely query patterns when adding a table, endpoint, report, or background job. Ask what will happen when the table is ten times larger, when a tenant has an unusually large dataset, or when several workers execute the same task together.

A database is exceptionally good at finding, joining, filtering, and aggregating data when given an achievable plan. Debugging queries means learning to see that plan, challenge unnecessary work, and validate improvements with measurement. Do that consistently, and performance bottlenecks become ordinary design problems instead of late-night surprises.