What is the MySQL equivalent of PostgreSQL's EXPLAIN ANALYZE

user_861039 0
Viewed 22919

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

postgresql mysql sql database
6 Answers

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.

user_50708790

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

user_5031440

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)
user_47045100
Related