Hands-on Lab·MySQL
MySQL Query Plans with EXPLAIN
EXPLAIN shows what the optimizer intends; EXPLAIN ANALYZE runs it and reports what happened. On 400,000 rows the difference between the estimate and the actual is the whole lesson.
Performance Guide 31 of 45 Intermediate
- OSUbuntu 26.04 LTS
- MySQL8.4.10-0ubuntu0.26.04.1
- Test data400,000 rows, 2 core / 4 GB host
- TimeAbout 16 min
EXPLAIN ANALYZE is 8.0.18+. It executes the query, so do not run it on a statement whose side effects you cannot afford.
| Server Name | IP Address | OS | Roles | CPU | RAM | HDD |
|---|---|---|---|---|---|---|
| db-a01 | 192.168.0.81 | Ubuntu 26.04 LTS | Primary / Source / Replica Set Member 1 | 2 Core | 4 GB | 50 GB |
This guide includes
Use this when a query is slow and you need to know what the server intends to do about it. This matters because EXPLAIN shows the plan while EXPLAIN ANALYZE runs it and reports what happened - and those are two different questions.
- building a table big enough to measure, with a recursive CTE
- reading a plan with no index available, through the four fields that carry the meaning
- measuring it with
EXPLAIN ANALYZErather than estimating
Before you start
- The
appdbdatabase. - Enough rows for a plan to matter - this guide builds 400,000.
-
Build a table worth measuring
-
Read a plan with no index available
-
Measure it rather than estimating it
Official sources
- MySQL 8.4 Reference Manual - Optimizing Queries with EXPLAIN
- MySQL 8.4 Reference Manual - Obtaining Execution Plan Information
- MySQL 8.4 Reference Manual - Multiple-Column Indexes
- MySQL 8.4 Reference Manual - Skip Scan Range Access
- MySQL 8.4 Reference Manual - The Slow Query Log
- MySQL 8.4 Reference Manual - InnoDB Locking