d7DetectabilityOperational 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.
e3EffortRemediation 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.
b5BurdenStructural 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.
t5TrapCognitive 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.
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
🧱FUNDAMENTALS— new to this? Start with the ground floor.
DatabasedatabaseA database is an organized collection of data stored electronically, designed so programs can efficiently retrieve, add, update, and delete information.
Nearly every application needs to remember information between sessions. Databases provide the reliable, fast, and organized storage that makes persistent data possible at any scale.
💡 Always use prepared statements with placeholders—never concatenate user input directly into database queries.
QuerygeneralA query is a request for specific information from a data source, most commonly a database. You write queries to ask questions like 'show me all users who signed up this month.'
Nearly every application needs to retrieve or update stored data. Understanding queries is essential because they're how your code communicates with databases—the foundation of dynamic websites, apps, and services.
💡 Always use prepared statements with placeholders instead of inserting variables directly into query strings.
Run EXPLAIN on every slow query — look for type:ALL and Using filesort. Add an index on the column in the WHERE or ORDER BY clause. Check with EXPLAIN again to confirm the index is used
