Master MySQL 8 Explain Analyze: Real Execution Insights & Performance Tuning

1. Explain Analyze Introduction

Explain is a common query analysis tool that evaluates how a query is executed, providing useful clues. However, it only offers estimates, not actual execution details such as row counts, which may differ from real results.

Explain Analyze is a new tool in MySQL 8 that can provide the actual execution details . It is a query performance analysis tool that shows where time is spent during query execution.

Explain Analyze generates a query plan and actually runs it, measuring real metrics at each key point—such as elapsed time, row count—and prints the detailed results.

2. Practical Effect

For example, consider the following query:

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

To run Explain Analyze, simply add it before the SELECT statement:

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

The execution result includes detailed information for each step. One of the steps looks like this:

Filter: (payment.payment_date like '2005-08%')
(cost=117.43 rows=894)
(actual time=0.454..194.045 rows=2844 loops=2)

Filter

indicates the filtering step. The line (payment.payment_date like '2005-08%') shows the filter condition. (cost=117.43 rows=894) is the optimizer's estimated cost and row count before execution. (actual time=0.454..194.045 rows=2844 loops=2) provides the real execution metrics: the first number (0.454) is the time to return the first row, and the second number (194.045) is the total time to return all rows. rows shows the actual number of rows returned, and loops indicates how many times the filter iterator was executed.

3. Real Environment

You can try it yourself using MySQL's sample database Sakila . Download it from https://dev.mysql.com/doc/index-other.html, extract, and load the schema and data:

mysql> SOURCE /path/to/sakila-schema.sql;
mysql> SOURCE /path/to/sakila-data.sql;

4. Summary

Thanks for reading; hope this helps.

Reference: https://mysqlserverteam.com/mysql-explain-analyze/ https://www.percona.com/blog/2019/10/28/using-explain-analyze-in-mysql-8/