Most database performance problems do not begin with an obviously bad query. They begin with a query that is acceptable on a small dataset, then becomes unpredictable as traffic, rows, and concurrent requests increase.

The goal is not to make every query clever. It is to make database work bounded, visible, and aligned with how the application actually reads and writes data. Predictable performance is a design property: it comes from choosing query patterns whose cost remains understandable as the system grows.

Start with bounded work

A request should rarely ask the database to consider an unbounded amount of data. Queries such as SELECT * FROM orders may be harmless in a development environment, but they establish a dangerous habit. Production data has a way of turning convenience into latency, memory pressure, and overloaded connections.

Set an explicit limit whenever the user does not genuinely need every matching record. Pair that limit with an ordering that is deterministic and supported by an index.

SELECT id, customer_id, status, created_at
FROM orders
WHERE customer_id = :customer_id
ORDER BY created_at DESC, id DESC
LIMIT 50;

This query communicates intent clearly: retrieve a recent, manageable slice of one customer’s orders. The matching index should reflect the access pattern, commonly beginning with customer_id and then the ordering columns where appropriate. Index design is not about indexing every field; it is about supporting the filters, joins, and sort orders the application relies on.

Prefer keyset pagination for deep result sets

Offset pagination is easy to write:

SELECT id, created_at, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 25 OFFSET 10000;

But large offsets ask the database to walk past rows it will not return. They can also produce awkward user experiences when rows are inserted or deleted between page requests. A record may appear twice, or disappear from the sequence.

For feeds, activity streams, audit logs, and other ordered collections, use keyset pagination. Pass the last row’s ordering values as a cursor.

SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 25;

The exact syntax and index behavior should be verified for the database engine in use, but the principle is stable: continue from a known position rather than skipping an ever-larger number of rows. Cursor values should be treated as part of the API contract, validated carefully, and based on a stable, unique ordering.

Eliminate N+1 queries at the boundary

The N+1 query problem often hides behind clean-looking application code. A PHP loop loads a list of records, then lazily loads a related record for every item. Fifty rows quietly become fifty-one database round trips.

First, decide what the endpoint needs. Then load that data deliberately. For a list of orders that needs customer names, a join may be appropriate:

SELECT
    o.id,
    o.status,
    o.created_at,
    c.name AS customer_name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.created_at >= :from
ORDER BY o.created_at DESC, o.id DESC
LIMIT 100;

For one-to-many relationships, joining everything into one large result can duplicate parent rows and inflate response payloads. In that case, two purposeful queries can be clearer: fetch the page of parent IDs, then fetch all required children with a constrained IN query. Group the results in application code. The important distinction is not “one query is always better”; it is “the number and shape of queries should not grow accidentally with the number of records displayed.”

Select data intentionally

SELECT * is convenient, but it couples a query to every column in a table. That can transfer large text fields or blobs that the endpoint never uses, make schema changes harder to reason about, and obscure what the application depends on.

Select the fields needed for the current operation. This improves readability as much as it can improve efficiency. A compact result set is easier to serialize, cache, test, and review.

The same discipline applies to aggregates. A dashboard should not load thousands of rows into PHP just to count or sum them. Let the database perform set-based work, while keeping the aggregation scope explicit.

SELECT status, COUNT(*) AS order_count
FROM orders
WHERE created_at >= :start
  AND created_at < :end
GROUP BY status;

Use a half-open time range, as shown above, rather than trying to manufacture an “end of day” timestamp. It avoids precision edge cases and makes adjacent reporting windows fit together cleanly.

Make writes small, atomic, and retry-aware

Read performance gets attention, but unpredictable writes are often more damaging. Keep transactions short: begin them close to the first write, do only the database work required for the atomic change, and commit promptly. Do not hold a transaction open while calling an external HTTP service, rendering a response, or waiting for user input.

When several changes must succeed or fail together, state that boundary with a transaction. For example, creating an order and reducing reserved inventory should not leave only one of those changes committed.

$pdo->beginTransaction();

try {
    $reserve = $pdo->prepare(
        'UPDATE inventory
         SET reserved = reserved + :quantity
         WHERE product_id = :product_id
           AND available - reserved >= :quantity'
    );
    $reserve->execute([
        'product_id' => $productId,
        'quantity' => $quantity,
    ]);

    if ($reserve->rowCount() !== 1) {
        throw new RuntimeException('Insufficient inventory.');
    }

    $order = $pdo->prepare(
        'INSERT INTO orders (customer_id, status)
         VALUES (:customer_id, :status)'
    );
    $order->execute([
        'customer_id' => $customerId,
        'status' => 'pending',
    ]);

    $pdo->commit();
} catch (Throwable $error) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    throw $error;
}

In a concurrent system, deadlocks and transient failures are possible even with sensible queries. Where the operation is safe to repeat, add a limited retry policy around the whole transaction. Do not blindly retry every exception: validation failures, unique-constraint violations, and permanent connectivity failures need different handling. Idempotency keys are especially valuable for externally initiated write APIs, because a client retry should not create a second order.

Use constraints as part of the query design

Application validation is useful, but the database remains the final authority over data integrity. Foreign keys, unique constraints, non-null columns, and appropriate check constraints turn assumptions into enforceable rules.

A unique constraint on an external payment reference, for example, provides a durable backstop against duplicate processing. A foreign key makes invalid relationships harder to create. These are not merely defensive measures; they simplify query logic because downstream code can rely on stronger invariants.

Inspect plans, not just elapsed time

A query that is fast today may be fast only because the table is still small or the cache is warm. When evaluating an important query, inspect its execution plan using the tools provided by the database. Look for full scans on large tables, expensive sorts, join strategies that do not match expectations, and estimates that differ sharply from actual row counts where that information is available.

Then test with realistic parameters. A query for one customer is not proof that the same query behaves well for a customer with years of history. Performance work improves when it is tied to a known request shape, row volume, index, and acceptable result size.

Build APIs that respect database reality

The database is not a passive storage layer beneath an unlimited API. Endpoint design determines query behavior. Filters need limits, searchable fields need a plan for indexing, exports need asynchronous or streamed workflows when result sets are large, and list endpoints need stable pagination rules.

Predictable performance comes from choosing boring, explicit patterns: narrow reads, stable orderings, supported indexes, bounded pages, deliberate relationship loading, short transactions, and enforceable constraints. None of these choices is glamorous. Together, they make the system easier to operate when it matters most: after the data and traffic have made guesswork expensive.