A slow page does not prove that the database is the problem, nor that an index is the solution. The same perception can originate in PHP code, connection saturation, an external HTTP call, response serialization, locks, or a query that returns too much data. Knowing how to investigate slow queries in PHP means building evidence before changing the schema or adding optimizations that could make writes more expensive.
Separate application latency from data latency
Start by breaking down a request's total time. Record an identifiable route or command, the start and end time, the queries executed, their durations, and external dependencies. Measuring average time is not enough: an API can appear healthy and still fail for certain filters, clients, or deep pages.
For each incident, it is useful to distinguish:
- PHP time: collection transformations, loops, JSON serialization, document generation, or excessive memory use.
- Database time: the duration of each query, lock waits, connection opening, and the number of rows transferred.
- Network and dependency time: remote caches, third-party APIs, file storage, queues, or identity services.
- Queue time: requests waiting for PHP workers, available connections, or database resources.
Use traces or structured logs with a request identifier. An isolated 20 ms query can become a problem lasting seconds if it runs hundreds of times in the same response. Conversely, a 500 ms query may not be the main cause if the process remains waiting for an external service for several seconds.
Collect evidence before changing code
Capture the parameterized query and, separately, representative parameters. Avoid logging secrets, complete personal data, or values that are not necessary to reproduce the case. A search for a common status does not behave the same as a search for a unique identifier; assessing only the convenient case leads to incorrect decisions.
The minimum evidence should include:
- The affected route, asynchronous job, or command, as well as its frequency.
- Observed duration, high percentiles, and when it occurs.
- SQL, typed parameters, and the number of executions per request.
- Rows returned and, when possible, rows read or examined.
- Approximate table size and the distribution of filtered values.
- Concurrency, simultaneous write operations, and relevant locks.
The engine's slow logs help identify candidates, but they do not replace an application trace: they usually do not indicate which endpoint built a query or how many times it was repeated. In MySQL and PostgreSQL, combine that information with connection, CPU, I/O, and wait-time metrics so you do not mistake a poor plan for temporarily saturated infrastructure.
Find patterns that multiply cost
Before analyzing a complex statement, look for common patterns. N+1 occurs when a list fetches its main rows and then runs an additional query for each relationship. Even if each individual query is fast, the volume of database round trips, planning work, and contention grows with page size.
Low-selectivity filters, such as very common statuses; functions applied to filtered columns; implicit type conversions; searches with a leading wildcard; sorting large result sets; and pagination with a high OFFSET are also suspicious. Requesting SELECT * can increase transfer, memory, and read work, even when the plan already uses an index.
The fix is not always a single query. Loading relationships in a controlled manner can solve an N+1, but unbounded eager loading can create a huge query or response. Define which relationships the route actually needs, limit their columns, and measure the effect with the expected page size.
Interpret the execution plan with real data
Run EXPLAIN to understand the proposed plan and use the variant that includes actual execution when it is safe and appropriate for the environment. In PostgreSQL, EXPLAIN ANALYZE executes the query; a modification statement should not be analyzed this way without understanding its effect. In MySQL, the available modes depend on the version and configuration, but the goal is the same: compare estimates with actual work.
A sequential scan is not automatically bad. If the query needs a large portion of a small or low-selectivity table, scanning it may cost less than jumping between the index and the table. Instead, investigate when the plan shows far more actual rows than estimated, costly sorts, temporary reads, joins over broad sets, or inner loops executed many times.
Useful questions when reviewing a plan
- How many rows did the optimizer expect, and how many did it actually process?
- Which node accounts for the most time, reads, or iterations?
- Is the filter applied early or after combining large sets?
- Does sorting occur over more rows than the response needs?
- Do the statistics reflect the current distribution of the data?
Discrepancies between estimates and reality may require updating statistics or reviewing types and conditions, rather than immediately creating an index. The plan is an explanation of an execution under certain parameters and load; it is not an automatic instruction to make a change.
Choose between rewriting, pagination, data access, and an index
First reduce unavoidable work. Select only the necessary columns, apply reasonable limits, remove unused relationships, and avoid moving complete histories into PHP only to filter them afterward. If the use case requires browsing a growing history, replace deep pagination with cursor- or key-based pagination: for example, continue from a stable combination of date and identifier instead of discarding thousands of rows with OFFSET.
Review joins and filters so they compare compatible columns and express the condition clearly. Sometimes it is preferable to query a relationship in batches; at other times, a single well-bounded query is better. The decision depends on cardinality, response volume, and frequency, not on a universal rule.
A composite index helps when it matches the access pattern. Its order matters: typically, equality and selective columns should facilitate filtering before those used for range or sorting, but the specific query and engine determine the outcome. An index can also help avoid a sort if it covers a compatible order, although not every combination of WHERE and ORDER BY allows this.
Avoid indexing columns merely because they appear in a condition. Indexes take up space, consume memory, and add work to INSERT, UPDATE, and DELETE. Redundant or low-value indexes can worsen a write-heavy system. Also review whether the index can retrieve the required columns without additional reads, but do not add covering columns without measuring the cost.
Hypothetical example: an operation history
Suppose a list shows operations by account, status, and date. As the history grows, page 200 degrades. The first hypothesis might be to create an index on the date. However, the trace reveals one main query followed by one query for each operation to fetch the responsible user: there is an N+1. The plan for the main query also reads many rows in order to discard earlier ones through OFFSET.
The reasonable sequence would be to load responsible users in batches or through a join limited to the necessary columns, replace deep pagination with a cursor based on created_at and id, and measure again. Only then should an index aligned with the account filter, stable ordering, and cursor be evaluated. The access change can reduce more work than an isolated index, and the final index must also be checked against the creation of new operations.
Validate under load and watch for write regressions
Compare before and after with representative parameters, data distribution, and concurrency. Measure duration, rows processed, reads, CPU usage, PHP memory, response size, and the number of queries per request. For a new index, also measure the latency and capacity of affected write operations.
Define explicit acceptance limits: for example, a verifiable reduction in the route's high percentile without unacceptably increasing creation or update time. Test empty cases, very common values, rare filters, first and last pages, and permissions that change the scope of the data.
Deploy changes observably and reversibly
When possible, separate the code deployment from index creation. Creating indexes can compete for resources or acquire locks depending on the engine, operation, and environment. Plan the timing, review the method supported by your database, and monitor duration, errors, and I/O pressure.
Introduce the new query gradually if the architecture allows it and maintain a clear rollback: restore the previous query, disable an alternative access path, or remove an index that proves harmful. A rollback does not replace backups or migration review, but it reduces exposure time in the face of unexpected behavior.
Checklist for a repeatable improvement
- Identify the route, symptom, and parameters that reproduce it.
- Separate PHP, database, network, and external dependency time.
- Quantify repetitions, rows returned, and rows processed.
- Look for N+1, deep pagination, low-selectivity filters, and sorting.
- Review the plan and compare its estimates with actual execution.
- Try data reduction, rewriting, and changing pagination before indexing.
- Design the index according to the complete filtering, sorting, and write pattern.
- Validate reads and writes with representative load, observability, and rollback.
