MySQL 8 performance, one move per page
The twenty-two moves past working queries: read the plan, build the index the query actually needs, and stop guessing which query is slow.
The query is slow. Here is the move.
MySQL 8 performance, one move per page
The twenty-two moves past working queries: read the plan, build the index the query actually needs, and stop guessing which query is slow.
Set in Space Grotesk, Inter and JetBrains Mono (SIL Open Font License).
Behaviour verified against the MySQL 8.0 Reference Manual: dev.mysql.com/doc/refman/8.0
Your purchase is for personal use only. You do not have redistribution rights: please do not share, resell, or republish this book or its pages.
© 2026 Steve Hodgkiss. All rights reserved. Personal use only; no redistribution rights.
Edition 1.0 · stevehodgkiss.net
Contents
Finding the work
The three moves that tell you which query is slow, what it planned, and what actually ran.
- 01The slow query log
- 02EXPLAIN: the plan
- 03EXPLAIN ANALYZE
The slow query log records statements that take more than long_query_time seconds; it is disabled by default, long_query_time defaults to 10 with microsecond resolution, and mysqldumpslow summarizes the log. From the MySQL 8.0 Reference Manual, The Slow Query Log. dev.mysql.com/doc/refman/8.0.
The slow query log
Let's say the dashboard is slow and everybody has a different theory. Guessing is slower than measuring.
The slow query log records anything over long_query_time. It's off by default. Set the threshold to a fraction of a second; the resolution is microseconds. Tuning queries you haven't measured is work on the wrong query.
The log names the culprit. Tune that one.
Run SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 0.2; then find your slowest query in the log, or summarize it with mysqldumpslow.
EXPLAIN shows the execution plan: type is the join type, key the index chosen, rows the estimate of rows to examine, and Extra carries extra operations like Using filesort or Using temporary. From the 8.0 manual, EXPLAIN Output Format. dev.mysql.com/doc/refman/8.0.
EXPLAIN: the plan
The query is slow. Before you touch an index, ask the database what it plans to do.
EXPLAIN prints the plan: type is the access method, key the index used, rows how many it expects to read. Extra names the extra work: Using filesort means an extra sort pass, Using temporary means a temp table. type ALL is a full table scan: every row, every time.
EXPLAIN before you tune. It's the estimate; the next move measures it.
Pick your slowest query and run EXPLAIN on it. Write down type, key, rows and Extra before changing anything.
EXPLAIN ANALYZE, 8.0.18+, executes the statement and reports estimated versus actual rows and per-iterator timings in milliseconds, always in TREE format; actual time and rows are per-loop averages, so multiply by loops. From the 8.0 manual, EXPLAIN Statement. dev.mysql.com/doc/refman/8.0.
EXPLAIN ANALYZE
EXPLAIN tells you the plan. It doesn't tell you where the plan was wrong.
EXPLAIN ANALYZE runs the query and annotates each step with actual time, rows and loops (8.0.18+). The actual numbers are per loop: a cheap-looking step with loops=9214 is a thousand small bills. Eyeballing the biggest printed number finds the wrong step: multiply time by loops, and never run it on a UPDATE you don't mean.
The plan is the estimate. ANALYZE is the receipt.
Run EXPLAIN ANALYZE on that slow query (a SELECT). Find the deepest step where estimated rows and actual rows diverge, and the step where actual time x loops is largest. Same place?
A multiple-column index is a sorted array of concatenated column values; the optimizer can use any leftmost prefix, so an index on (col1, col2, col3) serves (col1), (col1, col2) and (col1, col2, col3), but not (col2) alone. From the 8.0 manual, Multiple-Column Indexes. dev.mysql.com/doc/refman/8.0.
The composite index
Two indexes, one on status and one on created_at. The query filters on both, and uses neither well.
INDEX(status, created_at) is one sorted structure on the pair: equality columns first, the range column last. Its leftmost prefixes serve other queries too: status alone still works. A range column first (created_at, status) locks out everything after it: status becomes unusable.
Equalities first, the range last. Like a phone book.
Find a query filtering on two columns with single-column indexes on each. Replace them with one composite: equality column first, range column last. Compare the EXPLAIN.
EXPLAIN join types go from system and const, through eq_ref and ref, down to index and ALL; type ALL is a full table scan, and rows is the estimate of rows examined. From the 8.0 manual, EXPLAIN Output Format, Explain Join Types. dev.mysql.com/doc/refman/8.0.
type: the ladder
The type column reads like a mark scheme. Some values mean the index did the work; others mean brute force.
const and eq_ref are best: one row by primary or unique key, ref is a plain indexed lookup. index means the whole index is scanned, ALL the whole table. ALL on a big table in a JOIN's inner loop is the scan tax paid per outer row.
Read type before rows. ALL means no index survived the WHERE.
In your slowest query's EXPLAIN, write the type of each table on paper. Any ALL? That table is where the time goes.
The InnoDB buffer pool caches table and index pages in memory; on dedicated servers up to 80% of physical memory is often assigned to it, and pages age out on a modified LRU scheme. From the 8.0 manual, InnoDB Buffer Pool. dev.mysql.com/doc/refman/8.0.
The buffer pool
Same query, same plan: 4ms after a warm restart, 900ms on a cold Monday. The plan never changed.
The buffer pool is InnoDB's page cache in memory: hit it and there is no disk trip. On a dedicated server, up to 80% of RAM is often assigned to it. A tiny buffer pool makes every plan look slow: working data that doesn't fit is disk work.
Most 'slow database' stories are a working set that outgrew RAM.
Check your buffer pool size against the machine's RAM and your data size. If the hot data is bigger than the pool, that's the first bottleneck, not the queries.