← Back to glossary

Query Optimisation

Database Intermediate

debt(d7/e3/b5/t5)

d7 Detectability Operational debt — how invisible misuse is to your safety net

Closest to 'only careful code review or runtime testing' (d7). Slow queries are invisible to linters; they require running EXPLAIN, slow query logs, or profilers like Laravel Telescope/Symfony Profiler. Often only surface under production load (d9-ish), but APM/slow query logs make d7 fitting.

e3 Effort Remediation debt — work required to fix once spotted

Closest to 'simple parameterised fix' (e3). Per quick_fix, the typical remediation is adding an index or rewriting one query (e.g. keyset pagination, eager loading). Usually localised to a single query or migration, but may require schema changes so slightly above e1.

b5 Burden Structural debt — long-term weight of choosing wrong

Closest to 'persistent productivity tax' (b5). Query performance concerns shape ORM usage, pagination strategy, and indexing decisions across many features. Tags (database, performance) and the ORM misconception indicate this affects many work streams continuously.

t5 Trap Cognitive debt — how counter-intuitive correct behaviour is

Closest to 'notable trap' (t5). The misconception (ORMs generate optimised queries) plus gotchas like OFFSET pagination scaling poorly and functions on indexed columns disabling indexes are documented traps most devs eventually learn the hard way.

About DEBT scoring → scored by claude-opus-4-7 · 2026-05-11 · reviewed by human

Also Known As

SQL optimisation query tuning slow query EXPLAIN execution plan query performance

TL;DR

The process of rewriting SQL queries and database structures to reduce execution time — using EXPLAIN to identify full table scans, adding targeted indexes, rewriting JOINs, and eliminating N+1 patterns.

Explanation

Query optimisation starts with measurement: EXPLAIN (MySQL) or EXPLAIN ANALYZE (PostgreSQL) shows the query execution plan — whether indexes are used, how many rows are examined, and where time is spent. Key signals: 'type: ALL' or 'Seq Scan' means full table scan — usually fixed with an index; 'rows' column shows estimated rows examined — high values on narrow result sets suggest missing index; 'Using filesort' means ORDER BY cannot use an index — add an index on the ORDER BY column. Common optimisations: add indexes on WHERE, JOIN ON, and ORDER BY columns; use covering indexes that include SELECT columns; rewrite correlated subqueries as JOINs; use LIMIT with an indexed ORDER BY for pagination; avoid functions on indexed columns in WHERE (WHERE YEAR(created_at) = 2024 cannot use an index; WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31' can).

Common Misconception

✗ ORMs generate optimised queries automatically. ORMs generate correct queries, not necessarily efficient ones. Eloquent's with() (eager loading) is more efficient than lazy loading for relations, but chaining multiple with() calls on large collections, using pluck() without select(), or using whereHas() on large tables can all generate slow queries. Always log and review ORM-generated SQL in development using Laravel Telescope, Symfony Profiler, or PDO query logging.

Why It Matters

A single slow query can bring down an entire PHP application under load. A query taking 2 seconds on a page with 10 concurrent users means all 10 requests are blocking for 2 seconds each, exhausting PHP-FPM workers and causing a queue of requests to build up. After fixing the query, the same page handles 100 concurrent users. Query optimisation is the highest-leverage activity for PHP applications that have passed the initial build phase — it is common to find one or two queries responsible for 80% of database load.

Common Mistakes

Optimising without measuring first — always run EXPLAIN before changing a query; guesswork produces random results.
Using functions on indexed columns in WHERE clauses — WHERE LOWER(email) = ? prevents index use; store lowercase in the column or use a functional index.
Paginating with OFFSET on large tables — OFFSET 10000 LIMIT 20 scans 10,020 rows; use keyset pagination (WHERE id > last_seen_id LIMIT 20) instead.
Not using query caching for expensive read-heavy queries — a report query that takes 3 seconds but is identical for all users in a 5-minute window is a cache candidate.

Code Examples

✗ Vulnerable

-- Function on indexed column — can't use index
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- EXPLAIN: type=ALL, rows=500000

-- OFFSET pagination — scans skipped rows
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 50000;
-- Scans 50,020 rows to return 20

✓ Fixed

-- Range on indexed column — uses index
SELECT * FROM users
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31 23:59:59';
-- EXPLAIN: type=range, rows=1240 — uses index

-- Keyset pagination — constant time regardless of page
SELECT * FROM posts
WHERE id < :last_id  -- last ID from previous page
ORDER BY id DESC
LIMIT 20;
-- Scans exactly 20 rows every time

References

https://dev.mysql.com/doc/refman/8.0/en/using-explain.html