A slow SQL query is not solved sustainably by “adding an index” until you know which request runs it, how often it runs and whether the database work is actually the dominant part of that request. Measure query shape, frequency and rows examined together.
Tie the query to a request
Use application tracing, Query Monitor in a safe environment, database slow logs or APM to identify the page, REST call, cron task or admin action that triggers the query.
Measure frequency as well as duration
A 400 ms query once per hour and a 40 ms query 200 times per request have different priorities. Count executions and concurrency before optimising the single slowest sample.
Inspect the execution plan
Look at indexes used, rows examined, temporary tables, sorts and join strategy with realistic parameters. Do not add overlapping indexes simply because a column appears in WHERE.
Reduce unnecessary work at the application layer
Fix N+1 patterns, repeated metadata lookups, unbounded queries and requests for columns/rows the caller does not need. Database tuning cannot compensate indefinitely for wasteful query patterns.
Test candidate changes with representative data
Small staging databases can hide production cardinality problems. Use safe, production-like volumes and compare plan plus request latency before/after.
Verify the whole request improves
After changing query/index/application logic, confirm total request time, database load and error rate improve under representative concurrency. A faster query that increases writes or lock contention may be a regression elsewhere.
Correlate database time with PHP-FPM request tracing and compare with slow first-byte diagnosis before treating SQL as the only bottleneck.