Query Optimisation Techniques
IntermediateAdd indexes, rewrite correlated subqueries as JOINs, avoid SELECT *, use LIMIT, avoid functions on indexed columns in WHERE, and leverage the query cache (or ProxySQL) carefully.
Overview
Query optimisation is the process of making SQL statements run faster by reducing the work MySQL has to do — fewer rows scanned, fewer sorts, fewer temporary tables. The strategies form a hierarchy: first ensure the right indexes exist and the optimiser uses them, then rewrite inefficient query patterns (correlated subqueries, functions in WHERE), then tune schema and server configuration. EXPLAIN (and EXPLAIN ANALYZE) is the diagnostic tool used at every step. Most performance problems in MySQL applications trace back to a small number of root causes: missing or unused indexes, correlated subqueries inside loops, SELECT * over wide tables, and unparameterised queries that bypass the query plan cache.
The Core Rules — What to Always Check First
Before any deep analysis, apply these universal checks:
1. **Avoid functions on indexed columns in WHERE** — `WHERE YEAR(created_at) = 2024` cannot use an index on `created_at`. Rewrite as a range: `WHERE created_at >= "2024-01-01" AND created_at < "2025-01-01"`.
2. **Avoid SELECT *** — fetches all columns including large TEXT/BLOB fields; prevents covering-index optimisation.
3. **Use LIMIT** — add LIMIT to queries that do not need the entire result set; stops early in index scans.
4. **Avoid implicit type conversion** — `WHERE user_id = "123"` (string literal on INT column) disables the index. Types must match.
-- ❌ BAD: function on indexed column — full scan
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- EXPLAIN type: ALL (no index)
-- ✅ GOOD: rewrite as range — index used
SELECT id, total FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- EXPLAIN type: range, key: idx_created_at
-- ❌ BAD: implicit type conversion on user_id INT column
SELECT * FROM users WHERE user_id = '456'; -- string vs INT → full scan
-- ✅ GOOD: matching type
SELECT id, email FROM users WHERE user_id = 456;
-- ❌ BAD: SELECT * over wide table
SELECT * FROM products WHERE category_id = 5;
-- ✅ GOOD: select only needed columns — can use covering index
SELECT id, name, price FROM products WHERE category_id = 5;Rewrite Correlated Subqueries as JOINs
A correlated subquery re-executes for every row of the outer query — O(n) queries inside a query. This is one of the most common performance killers in application SQL. Rewrite as a JOIN (or LEFT JOIN + IS NULL for NOT EXISTS patterns). The optimiser sometimes does this automatically, but explicit JOINs are cleaner and always safe.
-- ❌ BAD: correlated subquery — re-runs for EVERY order row
SELECT id, customer_id
FROM orders o
WHERE total > (
SELECT AVG(total)
FROM orders
WHERE customer_id = o.customer_id -- correlates to outer row
);
-- Runs the subquery once per order row — O(n) subqueries!
-- ✅ GOOD: rewrite with JOIN + pre-aggregated subquery
SELECT o.id, o.customer_id
FROM orders o
JOIN (
SELECT customer_id, AVG(total) AS avg_total
FROM orders
GROUP BY customer_id
) avg_by_customer ON avg_by_customer.customer_id = o.customer_id
WHERE o.total > avg_by_customer.avg_total;
-- Single pass: GROUP BY once, then one JOIN
-- ❌ BAD: NOT IN with subquery (NULL-safe problem + correlated)
SELECT id FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);
-- ✅ GOOD: LEFT JOIN + IS NULL (faster, NULL-safe)
SELECT c.id FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.customer_id IS NULL;Pagination — Offset vs Keyset (Cursor) Pagination
LIMIT / OFFSET is convenient but scales poorly: `LIMIT 20 OFFSET 100000` scans and discards 100 000 rows before returning 20. For large datasets, use keyset (cursor) pagination — always filter by the last seen primary key or unique column from the previous page.
-- ❌ BAD: LIMIT + OFFSET — slow for large offsets
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;
-- MySQL scans 100,020 rows to return 20 — gets slower on each page
-- ✅ GOOD: Keyset (cursor) pagination — consistent O(log n) performance
-- First page:
SELECT id, title, created_at FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Returns rows with last row: created_at='2024-03-01 10:00', id=9500
-- Next page — use the last values as cursor:
SELECT id, title, created_at FROM articles
WHERE (created_at, id) < ('2024-03-01 10:00', 9500)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Uses index on (created_at, id) — scans exactly 20 rows every time
-- Index to support keyset pagination:
ALTER TABLE articles ADD INDEX idx_created_id (created_at DESC, id DESC);Key Points to Remember
- 1Wrap an indexed column in a function (YEAR(), LOWER(), etc.) in a WHERE clause and the index cannot be used — rewrite as a range.
- 2Correlated subqueries re-execute per outer row; rewrite as a JOIN with a pre-aggregated subquery for O(1) vs O(n) performance.
- 3SELECT * prevents covering-index optimisation and fetches unnecessary data — always select only needed columns.
- 4LIMIT/OFFSET pagination degrades at high offsets; use keyset (cursor) pagination with WHERE id > last_seen_id for consistent performance.
- 5Implicit type conversions (string literal on INT column) silently disable indexes — always match parameter types to column types.
- 6Use EXPLAIN ANALYZE after every optimisation to confirm the plan changed as expected and estimate improvements.
Interview Questions
Sign in to ask AriaWhy does WHERE YEAR(created_at) = 2024 cause a full table scan even if created_at is indexed?
What is a correlated subquery and why is it slow?
How does keyset pagination outperform LIMIT/OFFSET at scale?
You have a query hitting a composite index (a, b) but EXPLAIN shows a full scan. What could be wrong?
How would you optimise a query that calculates a running total for each row in a large table?
Ask Aria about Query Optimisation Techniques
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.