Doc Menu

EXPLAIN ANALYZE

The EXPLAIN ANALYZE statement works similar to EXPLAIN, with the major difference being that it will actually execute the statement. This allows you to compare the estimates used as part of query planning to actual values encountered during execution. If the estimates differ significantly from the actual values, you should consider running ANALYZE TABLE on the affected tables.

Note:

When you use EXPLAIN ANALYZE to execute DML statements, modification to data is normally executed. Currently, the execution plan for DML statements cannot be shown yet.

Synopsis

ExplainSym:

ExplainSym

ExplainStmt:

ExplainStmt

ExplainableStmt:

ExplainableStmt

EXPLAIN ANALYZE output format

Different from EXPLAIN, EXPLAIN ANALYZE executes the corresponding SQL statement, records its runtime information, and returns the information together with the execution plan. Therefore, you can regard EXPLAIN ANALYZE as an extension of the EXPLAIN statement. Compared to EXPLAIN, the return results of EXPLAIN ANALYZE include columns of information such as actRows, execution info, memory, and disk. The details of these columns are shown as follows:

attribute namedescription
actRowsNumber of rows output by the operator.
execution infoExecution information of the operator. time represents the total wall time from entering the operator to leaving the operator, including the total execution time of all sub-operators. If the operator is called many times by the parent operator (in loops), then the time refers to the accumulated time. loops is the number of times the current operator is called by the parent operator.
memoryMemory space occupied by the operator.
diskDisk space occupied by the operator.

Examples

CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.12 sec)
INSERT INTO t1 (c1) VALUES (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
EXPLAIN ANALYZE SELECT * FROM t1 WHERE id = 1;
+-------------+---------+---------+------+---------------+--------------------------+---------------+--------+------+
| id          | estRows | actRows | task | access object | execution info           | operator info | memory | disk |
+-------------+---------+---------+------+---------------+--------------------------+---------------+--------+------+
| Point_Get_1 | 1.00    | 1       | root | table:t1      | time:177.183µs, loops:2  | handle:1      | N/A    | N/A  |
+-------------+---------+---------+------+---------------+--------------------------+---------------+--------+------+
1 row in set (0.01 sec)
EXPLAIN ANALYZE SELECT * FROM t1;
+-----------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------+--------------------------------+-----------+------+
| id                    | estRows  | actRows | task      | access object | execution info                                                         | operator info                  | memory    | disk |
+-----------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------+--------------------------------+-----------+------+
| TableReader_5         | 10000.00 | 3       | root      |               | time:454.744µs, loops:2, rpc num: 1, rpc time:328.334µs, proc keys:0   | data:TableFullScan_4           | 199 Bytes | N/A  |
| └─TableFullScan_4     | 10000.00 | 3       | cop[tikv] | table:t1      | time:148.227µs, loops:4                                                | keep order:false, stats:pseudo | N/A       | N/A  |
+-----------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------+--------------------------------+-----------+------+
2 rows in set (0.00 sec)

MySQL compatibility

EXPLAIN ANALYZE is a feature of MySQL 8.0, but both the output format and the potential execution plans in TiDB differ substantially from MySQL.

See also