Description:
The user manual on EXPLAIN ANALYZE (see https://dev.mysql.com/doc/refman/8.0/en/explain.html#explain-analyze) does not specify the unit for timings printed in the output of the command. This becomes clear only from blog posts (e.g. see https://mysqlserverteam.com/mysql-explain-analyze/) and feature preview presentations (e.g. see https://www.slideshare.net/NorvaldRyeng/mysql-8018-latest-updates-hash-join-and-explain-an...). In this regard my first suggestion is to update the manual, so it's clear for all users what is the unit for timings.
However, I think it would be even better to print the unit in the output of the command, which is my second suggestion. For example, printing the abbreviation "ms" (see https://en.wikipedia.org/wiki/Millisecond) next to the timing values would make it explicitly clear what unit are these values in. It would also make it less confusing, because MySQL client for example prints query timings in seconds (and suffixes them with "sec").
How to repeat:
1. Execute EXPLAIN ANALYZE on an arbitrary query (that could be explained by the iterator executor of course). For example I use the following sample query over the dept_emp schema (see https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html):
EXPLAIN ANALYZE
SELECT E.ename, E.job, D.dname
FROM dept D,
emp E
WHERE E.deptno = D.deptno
AND E.job = 'CLERK';
Note: I have added 1 million random employees in emp table.
2. The result is:
+------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=144308.94 rows=442815)
(actual time=5.904..3281.472 rows=333278 loops=1)
-> Table scan on D (cost=1.40 rows=4)
(actual time=4.659..4.666 rows=4 loops=1)
-> Filter: (e.job = 'CLERK') (cost=5627.35 rows=110704)
(actual time=1.016..811.246 rows=83320 loops=4)
-> Index lookup on E using fk_deptno (deptno=d.deptno) (cost=5627.35 rows=332171)
(actual time=1.013..786.799 rows=250004 loops=4) |
+------------------------------------------------------------------------------------------+
1 row in set (3.3051 sec)
Note: Output is shortened (actual times and rows moved to next line) for better readability (i.e. shorter lines).
Suggested fix:
The expected output would be:
+------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=144308.94 rows=442815)
(actual time=5.904..3281.472 ms rows=333278 loops=1)
-> Table scan on D (cost=1.40 rows=4)
(actual time=4.659..4.666 ms rows=4 loops=1)
-> Filter: (e.job = 'CLERK') (cost=5627.35 rows=110704)
(actual time=1.016..811.246 ms rows=83320 loops=4)
-> Index lookup on E using fk_deptno (deptno=d.deptno) (cost=5627.35 rows=332171)
(actual time=1.013..786.799 ms rows=250004 loops=4) |
+------------------------------------------------------------------------------------------+
1 row in set (3.3051 sec)
Note: I'm not able to highlight in color, so note the "ms" after time=first..all strings.
