What is the MySQL equivalent of PostgreSQL's EXPLAIN ANALYZE

Question

I'd like to get a detailed query plan in MySQL similar to EXPLAIN ANALYZE shows in PostgreSQL. Is there an equivalent?

score 19 · Accepted Answer · edited Feb 08 '22 at 12:43

19

EDIT: While not a direct equivalent or as detailed as Explain Analyze here are some tools that you can look at

mysql offers EXPLAIN and procedure analyse()
https://dev.mysql.com/doc/refman/8.0/en/explain.html
http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html

edited Feb 08 '22 at 12:43

user4035

22,508
11
59
94

answered Jul 25 '11 at 06:58

plague

1,908
11
11

15

Although MySQL's explain will return an execution plan, it is by no means as detailed as PostgreSQL's output from EXPLAIN **ANALZYE**. I don't think there is anything in MySQL that will give such a detailed plan. – Jul 25 '11 at 07:14
I agree with horse, there is no real equivalent. I think you'd have to turn on some kind of extended statistics and run the query then use the output of explain and the logs to really get something similar. – Scott Marlowe Jul 25 '11 at 20:31

score 8 · Answer 2 · answered Jul 25 '11 at 08:11

8

I have not used PostgreSQL before MySQL has EXPLAIN EXTENDED which gives more information than EXPLAIN and may give you the information you are looking for.

answered Jul 25 '11 at 08:11

bash-

6,144
10
43
51

score 7 · Answer 3 · answered Sep 17 '19 at 16:40

MySQL 8.0.18 introduces natively EXPLAIN ANALYZE:

MySQL 8.0.18 introduces EXPLAIN ANALYZE, which runs a query and produces EXPLAIN output along with timing and additional, iterator-based information about how the optimizer's expectations matched the actual execution. For each iterator, the following information is provided:

Estimated execution cost

Estimated number of returned rows

Time to return first row

Time to return all rows (actual cost)

Number of rows returned by the iterator

Number of loops

EXPLAIN ANALYZE can be used only with SELECT statements.

score 6 · Answer 4 · answered Jun 26 '17 at 23:45

`EXPLAIN EXTENDED`

MariaDB/MySQL provide something called EXPLAIN EXTENDED. However there is no substitute for EXPLAIN ANALYZE. EXPLAIN EXTENDED provides no timing information whatsoever, and the internal break down is far less verbose.

Name: 'EXPLAIN'
Description:
Syntax:
EXPLAIN [explain_type] SELECT select_options

explain_type:
    EXTENDED
  | PARTITIONS

Or:

EXPLAIN tbl_name

The EXPLAIN statement can be used either as a way to obtain information
about how MySQL executes a statement, or as a synonym for DESCRIBE:

o When you precede a SELECT statement with the keyword EXPLAIN, MySQL
  displays information from the optimizer about the query execution
  plan. That is, MySQL explains how it would process the statement,
  including information about how tables are joined and in which order.
  EXPLAIN EXTENDED can be used to obtain additional information.

  For information about using EXPLAIN and EXPLAIN EXTENDED to obtain
  query execution plan information, see
  https://mariadb.com/kb/en/explain/.

o EXPLAIN PARTITIONS is useful only when examining queries involving
  partitioned tables. For details, see
  http://dev.mysql.com/doc/refman/5.5/en/partitioning-info.html.

o EXPLAIN tbl_name is synonymous with DESCRIBE tbl_name or SHOW COLUMNS
  FROM tbl_name. For information about DESCRIBE and SHOW COLUMNS, see
  [HELP DESCRIBE], and [HELP SHOW COLUMNS].

URL: https://mariadb.com/kb/en/explain/

For instance this is taken from this example,

EXPLAIN ANALYZE SELECT *
FROM history AS h1
WHERE EXISTS (
  SELECT 1
  FROM history AS h2
  WHERE h1.lead_id = h2.lead_id
  GROUP BY lead_id
  HAVING count(is_first OR NULL) > 1
);

Will produce something like this on PostgreSQL,

                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Seq Scan on history h1  (cost=0.00..82680.50 rows=1100 width=9) (actual time=0.048..0.065 rows=3 loops=1)
   Filter: (SubPlan 1)
   Rows Removed by Filter: 3
   SubPlan 1
     ->  GroupAggregate  (cost=0.00..37.57 rows=1 width=5) (actual time=0.007..0.007 rows=0 loops=6)
           Group Key: h2.lead_id
           Filter: (count((h2.is_first OR NULL::boolean)) > 1)
           Rows Removed by Filter: 0
           ->  Seq Scan on history h2  (cost=0.00..37.50 rows=11 width=5) (actual time=0.003..0.004 rows=2 loops=6)
                 Filter: (h1.lead_id = lead_id)
                 Rows Removed by Filter: 4
 Planning time: 0.149 ms
 Execution time: 0.123 ms
(13 rows)

While this is the MySQL equivalent,

+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id   | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|    1 | PRIMARY            | h1    | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using where |
|    2 | DEPENDENT SUBQUERY | h2    | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using where |
+------+--------------------+-------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

Little update MySQL 5.7+/MariaDB 10.1.2+ has `EXPLAIN FORMAT=JSON` which include relative query costs.. MySQL 5.6.3+ also has a option to trace the optimizer ( https://dev.mysql.com/doc/internals/en/tracing-example.html ) which in some ways can be used as PostgreSQL's `EXPLAIN ANALYZE` — Raymond Nijland, Apr 29 '18 at 14:24
According to the MySQL documentation, the EXPLAIN EXTENDED has no benefit anymore: "In older MySQL releases, partition and extended information was produced using EXPLAIN PARTITIONS and EXPLAIN EXTENDED. Those syntaxes are still recognized for backward compatibility but partition and extended output is now enabled by default, so the PARTITIONS and EXTENDED keywords are superfluous and deprecated. Their use results in a warning, and they will be removed from EXPLAIN syntax in a future MySQL release." — Ben Ootjers, Nov 28 '18 at 13:49

score 3 · Answer 5 · answered Nov 19 '13 at 18:44

just for clarity, comment on accepted answer (don't have enough karma to add comment)

procedure analyse() is for a different purpose that EXPLAIN, it analyzes the data set of specified column and suggests the best data type, i.e. it's useful when we have 1000 rows of varchar(255) and want to check how much length do we really need, f.e. it might tell that varchar(23) would suffice

UsamaAmjad · Answer 6 · 2022-01-24T13:45:05.650

2020 Update EXPLAIN ANALYZE Available

Old question but just for an update, with version 8.0.18 Explain Analyze is also available in MySQL and you can use it like below:

mysql> explain analyze select count(*) from sbtest1 where k > 500000\G

*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (actual time=178.225..178.225 rows=1 loops=1)
-> Filter: (sbtest1.k > 500000) (cost=98896.53 rows=493204) (actual time=0.022..147.502 rows=625262 loops=1)
-> Index range scan on sbtest1 using idx3 (cost=98896.53 rows=493204) (actual time=0.021..96.488 rows=625262 loops=1)

1 row in set (0.18 sec)

What is the MySQL equivalent of PostgreSQL's EXPLAIN ANALYZE

6 Answers6

`EXPLAIN EXTENDED`

Linked

Related