Skip to content

Get information via EXPLAIN ANALYZE

EXPLAIN ANALYZE is an analysis tool for queries that will show you how long SQL spends on queries and why. It will plan to query, detect it and execute it, while counting the number of rows and measuring the time spent at each point in the execution plan. After execution is complete, EXPLAIN ANALYZE prints the plan and measurement results instead of querying the results.

EXPLAIN ANALYZE, which runs the SQL statement to produce the EXPLAIN output, and in addition, it produces other information, such as time and iterator-based additional information, as well as the matching of the expected optimizer to actual execution.

For each iterator, provide the following information:

  • Estimated execution costs

The cost model does not consider some iterators and is therefore not included in the estimation.

  • Estimated number of returned rows

  • Return to the time of the first row

  • The time taken to execute this iterator (including only the child iterator, but not the parent iterator), in milliseconds.

  • The number of rows returned by the iterator

  • Number of loops

Query execution information is displayed in the TREE output format, where the node represents the iterator. EXPLAIN ANALYZE always uses the TREE output format.

EXPLAIN ANALYZE can be used with SELECT statements or with multi-table UPDATE and DELETE statements.

You can terminate this statement using KILL QUERY or CTRL-C.

EXPLAIN ANALYZE cannot be used with FOR CONNECTION.

Example

Create table

CREATE TABLE t1 (
    c1 INTEGER DEFAULT NULL,
    c2 INTEGER DEFAULT NULL
);

CREATE TABLE t2 (
    c1 INTEGER DEFAULT NULL,
    c2 INTEGER DEFAULT NULL
);

CREATE TABLE t3 (
    pk INTEGER NOT NULL PRIMARY KEY,
    i INTEGER DEFAULT NULL
);

Table output result:

> mysql> EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G
************************ 1. row************************
QUERY PLAN: Project
************************ 2. row*********************
QUERY PLAN: Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=0 InputSize=0bytes OutputSize=0bytes MemorySize=0bytes
************************ 3. row*********************
QUERY PLAN: -> Join
*************************** 4. row*********************
QUERY PLAN: Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=0 InputSize=0bytes OutputSize=0bytes MemorySize=16441bytes
************************ 5. row*********************
QUERY PLAN: Join Type: INNER
************************ 6. row*********************
QUERY PLAN: Join Cond: (t1.c1 = t2.c2)
************************ 7. row*********************
QUERY PLAN: -> Table Scan on tpch.t1
************************ 8. row*********************
QUERY PLAN: Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=0 InputSize=0bytes OutputSize=0bytes MemorySize=0bytes
*************************** 9. row*********************
QUERY PLAN: -> Table Scan on tpch.t2
*************************** 10. row*********************
QUERY PLAN: Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=0 InputSize=0bytes OutputSize=0bytes MemorySize=0bytes
10 rows in set (0.00 sec)

> EXPLAIN ANALYZE SELECT * FROM t3 WHERE i > 8\G
************************ 1. row************************
QUERY PLAN: Project
************************ 2. row*********************
QUERY PLAN: Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=0 InputSize=0bytes OutputSize=0bytes MemorySize=0bytes
************************ 3. row*********************
QUERY PLAN: -> Table Scan on tpch.t3
*************************** 4. row*********************
QUERY PLAN: Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=0 InputSize=0bytes OutputSize=0bytes MemorySize=0bytes
************************ 5. row*********************
QUERY PLAN: Filter Cond: (t3.i > 8)
5 rows in set (0.00 sec)

> EXPLAIN ANALYZE SELECT * FROM t3 WHERE pk > 17\G
************************ 1. row************************
QUERY PLAN: Project
************************ 2. row*********************
QUERY PLAN: Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=0 InputSize=0bytes OutputSize=0bytes MemorySize=0bytes
************************ 3. row*********************
QUERY PLAN: -> Table Scan on tpch.t3
*************************** 4. row*********************
QUERY PLAN: Analyze: timeConsumed=0ms waitTime=0ms inputRows=0 outputRows=0 InputSize=0bytes OutputSize=0bytes MemorySize=0bytes
************************ 5. row*********************
QUERY PLAN: Filter Cond: (t3.pk > 17)
5 rows in set (0.01 sec)