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
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
