Chapter 10
Chapter 10 — Query Optimisation
Every query you write goes through three stages: the parser checks syntax, the optimiser decides how to execute it (which indexes to use, what join order to pick, whether to use a temporary table), and the executor runs the chosen plan. The optimiser is very good — but it can only work with the indexes you give it and the SQL you write. This chapter teaches you to read its mind, feed it what it needs, and spot the patterns that defeat it.
The golden rule of optimisation: measure first, then fix.
Gut-feel optimisation creates complexity without guaranteed gain.
Use EXPLAIN to confirm there's a problem, the slow query log to find which queries
need attention, and benchmark before and after any change.
1. EXPLAIN — Reading the Query Plan
Prefix any SELECT (or DML statement in MySQL 8.0+) with EXPLAIN to see
the execution plan the optimiser chose — without actually running the query.
EXPLAIN
SELECT
b.title,
CONCAT(a.first_name, ' ', a.last_name) AS author,
COUNT(r.review_id) AS reviews
FROM books b
JOIN book_authors ba ON ba.book_id = b.book_id
JOIN authors a ON a.author_id = ba.author_id
LEFT JOIN reviews r ON r.book_id = b.book_id
GROUP BY b.book_id, b.title, a.first_name, a.last_name
ORDER BY reviews DESC\G
The key columns you need to read
| Column | What it tells you | Red flags |
|---|---|---|
| type | The access method (see graded list below) | ALL (full table scan), index (full index scan) |
| key | The index actually chosen (NULL if none) | NULL on a large table |
| key_len | Bytes of the index used — tells you how many columns of a composite index were used | Much shorter than expected → composite index not fully used |
| rows | Estimated rows examined per outer row — the optimiser's cost estimate | Very high relative to table size |
| filtered | Estimated % of rows that pass the WHERE after index access | Low (e.g. 5%) means index is poor for this filter |
| Extra | Additional plan details | Using filesort, Using temporary — expensive operations |
| possible_keys | Indexes the optimiser considered | NULL means no usable index exists for this table |
The type column — access method graded best to worst
const / eq_ref
Best. At most one row matched — primary key or unique index equality. As fast as a lookup can be.
ref
Good. Non-unique index equality — returns a small set of matching rows. Common on foreign key columns.
range
Acceptable. Index range scan — WHERE col BETWEEN, col > val, col IN (…). Reads a contiguous slice of the index.
index
Marginal. Scans the entire index tree (cheaper than a table scan, but still full scan). Often appears when a covering index is used.
ALL
Bad. Full table scan — reads every row. Acceptable only on tiny tables (<a few hundred rows). Investigate immediately on large tables.
Extra column — what the flags mean
| Extra value | Meaning | Fix |
|---|---|---|
| Using index | Covering index — no table row read needed | Great. No action needed. |
| Using where | WHERE filter applied after index access | Normal. Fine unless rows is very high. |
| Using filesort | ORDER BY couldn't use an index — sort in memory/disk | Add an index that covers the ORDER BY columns. |
| Using temporary | Intermediate result stored in a temp table (GROUP BY, DISTINCT, some subqueries) | Add an index covering the GROUP BY columns, or rewrite the query. |
| Using filesort + Using temporary | Sort after a temp table — double penalty | High priority to fix. Redesign index or query structure. |
| Using index condition | Index condition pushdown — filter applied inside the storage engine | Good. Engine filters before returning rows. |
2. EXPLAIN ANALYZE — Actual Execution Stats
EXPLAIN ANALYZE (MySQL 8.0.18+) actually runs the query and returns the
plan annotated with real timing and row counts alongside the estimates.
Use it to confirm whether the optimiser's estimates match reality.
EXPLAIN ANALYZE
SELECT b.title, COUNT(r.review_id) AS reviews
FROM books b
LEFT JOIN reviews r ON r.book_id = b.book_id
GROUP BY b.book_id
ORDER BY reviews DESC\G
-- Typical EXPLAIN ANALYZE output (tree format)
-> Sort: reviews DESC (actual time=12.4..12.5 rows=84 loops=1)
-> Table scan on <temporary> (actual time=0.0..0.4 rows=84 loops=1)
-> Aggregate using temporary table (actual time=11.1..11.8 rows=84 loops=1)
-> Nested loop left join (actual time=0.2..8.4 rows=312 loops=1)
-> Table scan on b (cost=9.3 rows=84) (actual time=0.1..0.5 rows=84 loops=1)
-> Index lookup on r using idx_reviews_book_id (book_id=b.book_id)
(cost=0.9 rows=3) (actual time=0.06..0.09 rows=3 loops=84)
Compare estimated vs actual rows. When the optimiser estimates 3 rows but
actually processes 30,000, its cost model is wrong — usually because table statistics are
stale. Run
ANALYZE TABLE tablename; to refresh them.
3. Index Strategy
Single-column index
The baseline. Index one column used frequently in WHERE, JOIN, or ORDER BY. InnoDB primary key is always a clustered index — the table rows are physically ordered by it.
Composite index
Index on (col_a, col_b, col_c). Left-prefix rule: queries must use col_a to benefit, then optionally col_b, then col_c. Skipping a column in the middle breaks the chain.
Covering index
A composite index that includes all columns the query needs — SELECT list + WHERE + ORDER BY. The engine never touches the table rows — EXPLAIN shows "Using index."
Prefix index
Index on the first N characters of a TEXT/VARCHAR column:
INDEX (email(20)). Saves space but cannot be a covering index and cannot be used for ORDER BY.Adding indexes to the bookshop schema
-- Foreign key columns (if not indexed automatically)
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_order_items_order ON order_items (order_id);
CREATE INDEX idx_order_items_book ON order_items (book_id);
CREATE INDEX idx_reviews_book ON reviews (book_id);
CREATE INDEX idx_reviews_customer ON reviews (customer_id);
-- Composite: date range reports that also filter by customer
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
-- Covering: the catalogue listing query touches only these columns
CREATE INDEX idx_books_cover ON books (publisher_id, price, title);
-- Prefix: email is long, only the first 20 chars are usually enough
CREATE INDEX idx_customers_email_pfx ON customers (email(20));
The left-prefix rule — composite index column order matters
-- Composite index: (customer_id, order_date)
-- ✅ Uses index — leads with customer_id
SELECT * FROM orders WHERE customer_id = 7 AND order_date >= '2026-01-01';
-- ✅ Uses index (partial) — customer_id alone still benefits
SELECT * FROM orders WHERE customer_id = 7;
-- ❌ Cannot use index — skips the leading column
SELECT * FROM orders WHERE order_date >= '2026-01-01';
Covering index — eliminate the table row lookup
-- Index: (publisher_id, price, title)
-- Query only touches those three columns → "Using index" in EXPLAIN
SELECT title, price
FROM books
WHERE publisher_id = 3
ORDER BY price;
Index selectivity — not all columns deserve an index
Selectivity = distinct values ÷ total rows. A column with low selectivity (e.g. a boolean, or a status column with 3 values) is a poor index candidate — the optimiser may prefer a full table scan anyway because too many rows match.
-- Check selectivity before creating an index
SELECT
COUNT(DISTINCT status) AS distinct_values,
COUNT(*) AS total_rows,
ROUND(COUNT(DISTINCT status) / COUNT(*), 4) AS selectivity
FROM orders;
-- selectivity of 0.0003 → poor index candidate alone
-- combine with a high-selectivity column in a composite index instead
4. Anti-Patterns That Defeat Indexes
1 — Function on the indexed column
-- ❌ YEAR() wraps the column — index on order_date is useless
SELECT * FROM orders WHERE YEAR(order_date) = 2026;
-- ✅ Rewrite as a range — index is used
SELECT * FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';
-- ❌ LOWER() on the column defeats the index
SELECT * FROM customers WHERE LOWER(email) = 'alice@example.com';
-- ✅ Store emails lowercased, or use a case-insensitive collation
SELECT * FROM customers WHERE email = 'alice@example.com';
-- (works if email column uses utf8mb4_unicode_ci collation)
2 — Implicit type cast
-- ❌ isbn is VARCHAR — passing an integer forces a cast on every row
SELECT * FROM books WHERE isbn = 9780142000670;
-- ✅ Pass the correct type
SELECT * FROM books WHERE isbn = '9780142000670';
3 — Leading wildcard LIKE
-- ❌ Leading % means MySQL cannot use a B-tree index
SELECT * FROM books WHERE title LIKE '%foundation%';
-- ✅ Trailing wildcard only — index is used
SELECT * FROM books WHERE title LIKE 'Foundation%';
-- For full-text search, use FULLTEXT index + MATCH … AGAINST
ALTER TABLE books ADD FULLTEXT INDEX ft_title (title);
SELECT * FROM books
WHERE MATCH(title) AGAINST ('foundation' IN BOOLEAN MODE);
4 — OR across different columns
-- ❌ OR on two columns — optimiser often falls back to full scan
SELECT * FROM books
WHERE title = 'Dune' OR isbn = '9780441013593';
-- ✅ Rewrite as UNION — each branch can use its own index
SELECT * FROM books WHERE title = 'Dune'
UNION
SELECT * FROM books WHERE isbn = '9780441013593';
5 — Negation operators
-- ❌ != / NOT IN / NOT LIKE rarely use indexes efficiently
SELECT * FROM orders WHERE status != 'pending';
-- ✅ Rewrite positively when the positive set is small
SELECT * FROM orders
WHERE status IN ('shipped', 'delivered', 'cancelled');
6 — Non-deterministic functions in WHERE
-- ❌ NOW() is evaluated per row in some contexts — use a parameter instead
SELECT * FROM orders
WHERE order_date >= NOW() - INTERVAL 30 DAY;
-- This is actually fine — NOW() is a constant for the query duration
-- but DATE(order_date) > ... wraps the column — avoid that form
5. The Slow Query Log
The slow query log records every query that takes longer than a threshold you set. It's the most reliable way to find real problems in production — not guessed ones.
Enable the slow query log
-- Check current status
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- Enable for this session (doesn't persist across restart)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- log queries > 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- Also log queries that perform a full table scan (no index used)
SET GLOBAL log_queries_not_using_indexes = 'ON';
# Add to /etc/mysql/mysql.conf.d/mysqld.cnf for persistence:
# [mysqld]
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/slow.log
# long_query_time = 1
# log_queries_not_using_indexes = 1
Reading the slow query log with mysqldumpslow
# Summarise the top 10 slowest query patterns
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t = sort by total time
# -s c = sort by call count (finds frequently-run slow queries)
# -t 10 = top 10
# Typical slow query log entry
# Time: 2026-06-15T09:14:22
# User@Host: admin[admin] @ localhost []
# Query_time: 3.842134 Lock_time: 0.000120
# Rows_sent: 1 Rows_examined: 487503
# SET timestamp=1750000462;
# SELECT * FROM orders WHERE YEAR(order_date) = 2026;
Rows_examined vs Rows_sent is your key ratio.
Examining 487,503 rows to send 1 means the query is doing an enormous amount of
unnecessary work. A well-indexed query should examine close to the number of rows it returns.
6. Live Diagnosis
SHOW PROCESSLIST — what's running right now
SHOW FULL PROCESSLIST\G
-- Kill a stuck query (replace 42 with the Id from PROCESSLIST)
KILL QUERY 42; -- kills the query, keeps the connection
KILL 42; -- kills query AND disconnects
Key SHOW STATUS counters
-- Reset counters then run your workload, then check
FLUSH STATUS;
-- After running queries:
SHOW STATUS LIKE 'Handler_%';
-- Important counters:
-- Handler_read_rnd_next — rows read by full table scan (should be low)
-- Handler_read_key — index lookups (higher is better)
-- Handler_read_next — index range scan rows (acceptable)
-- Created_tmp_disk_tables — temp tables spilled to disk (should be 0)
-- Select_full_join — joins with no index (should be 0)
SHOW STATUS LIKE 'Select_full%';
SHOW STATUS LIKE 'Created_tmp%';
ANALYZE TABLE — refresh statistics for the optimiser
-- After large INSERT/DELETE/UPDATE batches, statistics can become stale
ANALYZE TABLE orders, order_items, books;
-- Check when a table was last analyzed
SELECT table_name, update_time, table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY update_time DESC;
7. A Complete Optimisation Walk-Through
A slow report query has been flagged in the slow query log — 4.2 seconds, examining 620,000 rows, returning 28. Here's the full diagnosis and fix cycle:
Step 1: run EXPLAIN
EXPLAIN
SELECT
DATE_FORMAT(o.order_date, '%Y-%m') AS month,
COUNT(DISTINCT o.order_id) AS orders,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE YEAR(o.order_date) = 2026
GROUP BY month
ORDER BY month\G
| table | type | key | rows | Extra |
|---|---|---|---|---|
| o | ALL | NULL | 420000 | Using where; Using temporary; Using filesort |
| oi | ref | idx_order_items_order | 3 | NULL |
Diagnoses: (1) YEAR() wraps the column — index on order_date is bypassed. (2) No index exists for ORDER BY month. (3) GROUP BY forces a temporary table.
Step 2: fix the query and add an index
-- Fix 1: rewrite the date filter as a range
-- Fix 2: add a composite index for date + order_id to cover the GROUP BY
CREATE INDEX idx_orders_date_id ON orders (order_date, order_id);
-- Rewritten query
SELECT
DATE_FORMAT(o.order_date, '%Y-%m') AS month,
COUNT(DISTINCT o.order_id) AS orders,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.order_date >= '2026-01-01'
AND o.order_date < '2027-01-01'
GROUP BY month
ORDER BY month;
Step 3: verify with EXPLAIN
| table | type | key | rows | Extra |
|---|---|---|---|---|
| o | range | idx_orders_date_id | 28 | Using where; Using index |
| oi | ref | idx_order_items_order | 3 | NULL |
Rows examined dropped from 420,000 to 28. "Using temporary" and "Using filesort" are gone. Query time: 12ms.
Quick Reference
| Tool / concept | Command / rule | What to act on |
|---|---|---|
| EXPLAIN | EXPLAIN SELECT … |
type = ALL, key = NULL, Extra = Using filesort / Using temporary |
| EXPLAIN ANALYZE | EXPLAIN ANALYZE SELECT … (8.0.18+) |
Large gap between estimated and actual rows → run ANALYZE TABLE |
| Access types (best→worst) | const → eq_ref → ref → range → index → ALL | Investigate anything at index or ALL on tables > a few hundred rows |
| Composite index order | Put equality columns first, range/ORDER BY column last | Left-prefix rule: skipping a column breaks index use for subsequent columns |
| Covering index | Include SELECT + WHERE + ORDER BY cols in one index | EXPLAIN shows "Using index" — no table row read. Fastest possible read path. |
| Function on column | WHERE YEAR(col) → WHERE col BETWEEN … | Any function wrapping an indexed column defeats the index |
| Leading wildcard | LIKE '%term' → use FULLTEXT index + MATCH … AGAINST | B-tree indexes require a known prefix |
| NOT IN with NULLs | Add WHERE col IS NOT NULL inside subquery |
Any NULL in the list silently empties the NOT IN result |
| Slow query log | SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; |
High Rows_examined vs Rows_sent ratio → missing or wrong index |
| mysqldumpslow | mysqldumpslow -s t -t 10 /path/slow.log |
Find the top time-wasting query patterns across thousands of log entries |
| Refresh statistics | ANALYZE TABLE tbl; |
Run after large bulk operations; stale stats cause poor plan choices |
| SHOW PROCESSLIST | SHOW FULL PROCESSLIST\G |
Long-running queries holding locks; kill with KILL QUERY id |
| Selectivity check | COUNT(DISTINCT col) / COUNT(*) |
Values near 0 → poor standalone index candidate; combine in composite or skip |
Course Complete
You've covered all 10 chapters of the Advanced SQL course — from normalisation and every join type through subqueries, CTEs, window functions, views, stored procedures, triggers, events, and query optimisation. The skills in this course represent the full professional SQL toolkit used in production databases worldwide.