Database performance problems rarely announce themselves with a neat explanation. They arrive as a slow endpoint, a queue that falls behind, a timeout during peak traffic, or a dashboard that feels inexplicably heavy. The tempting response is to add an index, increase a connection limit, or move to larger infrastructure.

Those changes can help, but they are guesses until you can see what the database is actually doing. Pragmatic performance work starts with evidence: which queries run, how often they run, how much data they touch, and where time is spent.

Start with the question behind the slowness

“The database is slow” is not a useful diagnosis. A database may be waiting on disk, spending CPU sorting rows, handling too many concurrent connections, or simply receiving an inefficient query pattern from the application.

Turn a vague symptom into a testable question. For example:

• Which SQL statement dominates request latency?
• Is one query slow, or is it executed hundreds of times per request?
• Does the query scan far more rows than it returns?
• Are writes delayed by locks or by application-side transaction handling?
• Does performance degrade only when concurrency increases?

This framing prevents premature fixes. An index designed for a query that is not actually important is maintenance overhead, not optimization.

Use the slow query log as a reality check

For MySQL-compatible databases, the slow query log is one of the most practical starting points. It records statements that exceed a configured threshold and can reveal queries that are both expensive and frequent.

A low threshold is useful during focused investigation, but it should be chosen carefully in production. Logging every statement can create unnecessary overhead and a large volume of data. The goal is to identify meaningful candidates, not collect noise indefinitely.

Once you have a candidate query, do not judge it only by its individual duration. A query taking 20 milliseconds may be more damaging than a one-second administrative report if the first runs thousands of times per minute.

Look for the combination of execution time, call frequency, and affected user paths. That is where optimization tends to produce real value.

Read execution plans before adding indexes

An execution plan explains how the database intends to retrieve and combine rows. In MySQL, EXPLAIN is the usual first tool:

EXPLAIN
SELECT id, title, published_at
FROM articles
WHERE author_id = 42
  AND status = 'published'
ORDER BY published_at DESC
LIMIT 20;

The output is not a verdict; it is a model of the chosen strategy. Pay particular attention to whether the database examines a large number of rows, uses an expected index, creates a temporary table, or performs a filesort. These signals need context. A filesort is not automatically wrong, and an index is not automatically useful.

For this query, a composite index such as (author_id, status, published_at) may be relevant because it follows the filtering and ordering pattern. But it should be validated against the actual query shape and data distribution. Index order matters, and an index that helps one read path can increase the cost of inserts and updates.

When supported by the database version and operational policy, EXPLAIN ANALYZE can add actual execution information. Use it thoughtfully, especially on production systems, because measurement itself runs the query.

Find the application patterns SQL cannot show alone

Database tools show statements. Application observability shows why those statements exist. This is particularly important in PHP applications using an ORM or repository layer, where convenient object access can hide expensive behavior.

The classic example is the N+1 query pattern. An endpoint fetches a list of orders, then loads the customer for each order individually. The page may work perfectly with ten rows and become costly with hundreds.

Measure query count and total database time per request. A request that performs 80 fast queries can still be slower, less predictable, and harder to scale than one that performs three well-designed queries.

Useful request-level signals include:

• total query count;
• total time spent waiting for the database;
• the slowest individual query;
• duplicate query fingerprints;
• rows returned versus rows inspected, where available.

Tracing is especially valuable when a request crosses APIs, queues, caches, and multiple database calls. It helps distinguish a slow database query from time spent waiting for a connection, serializing a response, or calling a downstream service.

Measure connection pressure and lock behavior

Not every database delay is caused by query execution. Connection exhaustion can make healthy queries appear slow because requests wait before SQL begins. This often happens when application workers, background jobs, and scheduled tasks each maintain their own pool without a shared concurrency budget.

Track active connections, waiting connections, connection errors, and the lifetime of idle connections. In containerized deployments, also consider multiplication effects: increasing the number of PHP workers or Docker replicas can multiply possible database connections faster than expected.

For write-heavy systems, inspect lock waits and transaction duration. A transaction that holds locks while performing HTTP calls, complex application work, or user interaction is a design problem. Keep transactions narrow: validate what you can first, perform the necessary database work, then commit promptly.

Retries deserve equal care. Retrying a transient deadlock can be reasonable when the operation is safe to repeat. Retrying every database error is not. A retry must have a limit, backoff, observability, and idempotency where duplicate execution could cause harm.

Benchmark the workflow, not just the query

A query copied into a console may look fast while the real endpoint remains slow. Production behavior includes parameter variation, cache state, network latency, serialization, concurrent traffic, and data growth.

Use representative data and realistic request paths. Compare before and after measurements under the same conditions. Record the change you made, the expected outcome, and the observed result. If an index reduces one endpoint’s latency but causes unacceptable write overhead elsewhere, that trade-off should be visible rather than accidental.

A practical optimization loop is simple:

1. Identify a user-visible or operationally meaningful symptom.
2. Measure the responsible request, job, or SQL pattern.
3. Inspect the execution plan and surrounding application behavior.
4. Make one targeted change.
5. Measure again and keep or revert based on evidence.

Make performance knowledge part of the system

The strongest database teams do not rely on a hero debugging session whenever latency rises. They build a small, durable set of signals into normal operations: slow-query visibility, request tracing, connection metrics, error rates, and alerts tied to user impact.

They also treat schema changes as software changes. New indexes, migrations, query rewrites, and connection-pool adjustments deserve review, testing, rollout planning, and a rollback path. Performance work becomes safer when it is repeatable.

The memorable shift is this: database tuning is not a contest to write clever SQL or collect indexes. It is a discipline of replacing assumptions with measurements. Once you can see the work your database performs, the next decision becomes far less mysterious—and far more likely to help.