Understanding MySQL 8.0.18 EXPLAIN ANALYZE: Usage, Output, and Performance Insights

MySQL 8.0.18 introduces EXPLAIN ANALYZE , a tool that executes a query and reports the actual execution time and row counts for each iterator, allowing developers to compare estimated costs with real performance.

Using the Sakila sample database, the article demonstrates a simple query that aggregates payment totals per staff member for August 2005, shows the standard EXPLAIN FORMAT= TREE output, and then runs EXPLAIN ANALYZE to display actual timings, rows processed, and loop counts.

SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment
  ON staff.staff_id = payment.staff_id
WHERE payment_date LIKE '2005-08%'
GROUP BY first_name, last_name;

Key metrics such as first‑row time, total time, rows read, and loop iterations are explained, with examples showing how to interpret differences between estimated and actual values, especially when filters or index lookups are involved.

The article provides practical tips: check execution times to locate bottlenecks, compare estimated vs. actual row counts to identify mis‑estimates, and use the information to consider alternative plans or improve statistics.

Overall, EXPLAIN ANALYZE , together with EXPLAIN FORMAT= TREE and OPTIMIZER TRACE , equips MySQL users with deeper insight into query execution for performance tuning.