Query Optimisation
debt(d5/e5/b5/t5)
Closest to 'specialist tool catches' (d5), slow query log, laravel-debugbar, clockwork, and pt-query-digest from detection_hints catch slow queries but require explicit profiling — not silent in prod but not caught by default lint either.
Closest to 'simple parameterised fix' (e3) but bumped to e5 because per quick_fix multiple changes are needed (EXPLAIN analysis, rewrite subqueries as JOINs, add indexes, narrow SELECT), often touching schema migrations plus query code across a feature.
Closest to 'persistent productivity tax' (b5), since applies_to spans web/cli/queue-worker contexts and slow queries become a recurring tax on many work streams; not architecturally load-bearing on their own.
Closest to 'notable trap' (t5), the misconception that 'adding an index always fixes a slow query' is a classic documented gotcha — devs eventually learn about function-on-column predicates and covering indexes, but the obvious fix often fails.
Also Known As
TL;DR
Explanation
Query optimisation encompasses: using EXPLAIN to understand query plans, adding appropriate indexes, avoiding SELECT * (fetch only needed columns), rewriting correlated subqueries as joins, avoiding functions on indexed columns in WHERE clauses (e.g., WHERE DATE(created_at) = '2025-01-01'), using query caching where appropriate, and batching inserts. In PHP applications, an ORM's query builder can hide inefficient patterns — always profile queries in production using slow query logs or APM tools.
Diagram
flowchart TD
SLOW[Slow query] --> EXPLAIN2[EXPLAIN ANALYZE]
EXPLAIN2 --> SEQSCAN{Sequential scan?}
SEQSCAN -->|yes| INDEX2[Add index on WHERE columns]
SEQSCAN -->|no| ROWS{Row estimate accurate?}
ROWS -->|way off| ANALYZE2[ANALYZE table<br/>update statistics]
ROWS -->|accurate| QUERY_SHAPE[Review query shape]
subgraph Query_Shape_Fixes
SELECT_STAR[SELECT star - fetch only needed columns]
N1_FIX[N+1 - use eager loading]
FUNC[Function on indexed column<br/>use index on expression instead]
end
INDEX2 & ANALYZE2 --> MEASURE[Re-measure execution time]
style SLOW fill:#f85149,color:#fff
style INDEX2 fill:#238636,color:#fff
style MEASURE fill:#1f6feb,color:#fff
Common Misconception
Why It Matters
Common Mistakes
- Not using EXPLAIN/EXPLAIN ANALYZE before optimising — guessing without the query plan is almost always wrong.
- SELECT * in queries — fetches unused columns, increasing data transfer and memory.
- Functions on indexed columns in WHERE: WHERE YEAR(created_at) = 2024 — prevents index use.
- Not rewriting correlated subqueries as JOINs — correlated subqueries execute once per outer row.
Code Examples
-- SELECT * fetches all columns — wastes memory and prevents index-only scans
SELECT * FROM orders WHERE status = 'pending';
-- Function on indexed column prevents index use
SELECT * FROM users WHERE YEAR(created_at) = 2024;-- Select only needed columns
SELECT id, user_id, total FROM orders WHERE status = 'pending';
-- Range instead of function — allows index scan
SELECT * FROM users
WHERE created_at >= '2024-01-01'
AND created_at < '2025-01-01';