It is the database, not PHP
When a business application is slow, PHP execution time is rarely the cause. The time is spent waiting for database queries, and usually for far more of them than anyone realised.
We have seen a single page issuing four hundred queries because a loop fetched related records one at a time. No amount of PHP optimisation fixes that.
The three usual causes
- Query multiplication — one query per row in a loop, instead of one query total
- Missing indexes on columns used for filtering or joining
- Over-fetching — selecting everything when three columns are needed
Find them before fixing them
- Log query count and total time per request
- Identify the slowest pages by real user timing, not by impression
- Look at the query log for repeated similar queries
- Use the database's own explain facility on the slow ones
Optimising without measuring produces a lot of work on the wrong queries, which is the most common way performance projects waste money.
Indexes are usually the quick win
A missing index on a foreign key or a frequently filtered column can turn a query from milliseconds into seconds. Adding it is a one-line change with an immediate effect.
Index the columns you filter, join and sort by. Do not index everything — each index costs on writes.
Caching comes after
| Do first | Then consider |
|---|---|
| Fix query multiplication | Query result caching |
| Add missing indexes | Object caching |
| Fetch only needed columns | Full page caching |
| Paginate large results | A read replica |
Caching a badly-written query hides the problem rather than solving it, and the problem reappears the moment the cache misses.