When a slow endpoint appears, the first instinct is often, “Add an index.” Sometimes that is exactly right. More often, it is the beginning of the investigation, not the end.

Indexes are one of the most valuable tools in database design, but they cannot repair an unclear data model, an inefficient query shape, an overloaded connection pool, or an API that asks for far more data than it needs. Real performance work starts by treating latency as a system property: application code, database behavior, infrastructure, and product requirements all participate.

Start with the actual bottleneck

A database can be slow for very different reasons. A query may scan too many rows, but it may also be fast in isolation and slow under concurrent load. The application may issue the same query hundreds of times per request. A container may have insufficient memory, causing useful database pages to fall out of cache. An ORM may quietly retrieve large object graphs that the response never uses.

Before changing schema or code, make the slow path observable. Capture the endpoint, its request volume, timing, query count, and representative query parameters. Then inspect the query plan for the actual query being run. The important question is not “does this column have an index?” It is “what work is the database doing, and why?”

A plan can reveal full scans, expensive sorts, poor join order, large intermediate result sets, or estimates that do not resemble reality. Those findings point to different fixes. Adding an index without understanding the plan can increase write cost and storage use while leaving the user-visible problem untouched.

Make the query smaller before making it faster

The cheapest row is the one you never read. Query design should reflect the data required by the feature, not the convenience of a broad model method.

Consider a dashboard that displays a customer name, the date of the latest order, and an order count. Fetching every order and calculating those values in PHP pushes unnecessary data across the network and consumes application memory. A focused aggregate query is usually a better boundary:

SELECT
    c.id,
    c.name,
    MAX(o.created_at) AS latest_order_at,
    COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.account_id = ?
GROUP BY c.id, c.name
ORDER BY latest_order_at DESC
LIMIT 50;

This is not automatically perfect; the right indexes and semantics still matter. But it expresses the feature directly and prevents the application from doing work the database is designed to perform.

Be equally suspicious of SELECT *. It couples an endpoint to every present and future column, increases transfer costs, and can accidentally pull large text or JSON fields into hot paths. Select the columns the response needs. This improves performance, makes API contracts clearer, and reduces the chance that an innocent schema addition becomes an expensive read.

Pagination is a performance decision

Offset pagination is easy to explain, which makes it tempting:

SELECT id, created_at, title
FROM posts
WHERE author_id = ?
ORDER BY created_at DESC, id DESC
LIMIT 25 OFFSET 5000;

For shallow pages, it may be entirely acceptable. For deep navigation over a large, changing dataset, the database still has to walk past earlier rows. The result can become increasingly expensive, and inserts can make page boundaries unstable.

Cursor, or keyset, pagination gives the database a more useful place to resume:

SELECT id, created_at, title
FROM posts
WHERE author_id = ?
  AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 25;

The cursor must align with a deterministic ordering, and the index should support the filtering and ordering pattern. The important lesson is broader than pagination: ask the database to continue from a known position instead of repeatedly rediscovering one.

Indexes must match access patterns

Indexes are most effective when they support a real predicate, join, and ordering pattern. A composite index is not simply several single-column indexes bundled together. Column order matters.

Suppose an API frequently asks for recent paid invoices for one account. A likely access pattern is:

SELECT id, issued_at, total
FROM invoices
WHERE account_id = ?
  AND status = 'paid'
ORDER BY issued_at DESC
LIMIT 100;

An index shaped around account_id, status, and issued_at may serve this query well, subject to the database engine and the distribution of values. But do not turn that observation into a rule that every filter column belongs in every index. Low-selectivity fields, write-heavy tables, and competing query patterns require judgment.

Every index has a maintenance cost on inserts, updates, deletes, backups, and storage. Review indexes as part of schema design, not as emergency debris left behind after incidents.

Stop accidental query multiplication

One slow query is visible. One hundred modest queries can be worse.

The classic example is an API that loads a list of orders and then loads the customer for each order. In PHP, this may look harmless because the relationship access is concise. At runtime, it becomes an N+1 query pattern: one query for the list, then one additional query per row.

Fix this deliberately. Use an appropriate join, batch-load related records, or configure eager loading with a constrained field list. Then verify the request-level query count. Avoid replacing N+1 with a giant eager load that retrieves every nested relation; load only the relations and columns required for that response.

Use caching as a contract, not a bandage

Caching can protect a database from repeated work, but only when the cached value has clear ownership, expiry, and invalidation behavior. “Cache it for an hour” is not a strategy if users expect changes to appear immediately.

Good cache candidates tend to be expensive, frequently read, and tolerant of bounded staleness: configuration snapshots, public catalog slices, derived summaries, or permission data with a carefully designed invalidation path. Cache keys should include every input that changes the result, such as tenant, locale, filters, and authorization scope.

Also plan for misses. A popular key expiring simultaneously across many workers can create a burst of identical database work. Request coalescing, stale-while-revalidate approaches, or controlled refreshes can reduce that risk when the system warrants the added complexity.

Performance includes the deployment environment

A well-written query can still suffer in a poorly configured runtime. Database connections are finite resources. If PHP workers, queue consumers, and scheduled jobs all open connections without a shared limit, load spikes can exhaust the database before CPU appears busy.

Set concurrency and pool limits with the database capacity in mind. Monitor connection usage, slow queries, lock waits, cache behavior, CPU, memory, and disk latency together. In Docker-based environments, also make resource limits explicit and ensure persistent database storage is appropriate for the workload. Containers improve packaging consistency; they do not make storage latency or memory pressure disappear.

Locking deserves the same attention as read performance. Keep transactions short, avoid interactive work inside them, update rows in a consistent order when multiple rows are involved, and make retry behavior intentional for transient conflicts. A retry without idempotency can duplicate side effects; an idempotency key or a durable uniqueness constraint is often part of the correct API design.

Build a habit of measured improvement

Database performance is rarely solved by one heroic optimization. It improves when teams make query shape, schema evolution, and operational behavior visible in everyday development. Measure a representative workload, change one meaningful thing, inspect the result, and keep the improvement only when it helps the real path.

Indexes remain essential. They simply work best as part of a larger discipline: retrieve less data, avoid repeated queries, choose pagination deliberately, model cache behavior honestly, and operate the database as a constrained shared service. That is how a database stops being a mysterious bottleneck and becomes a dependable part of the system’s design.