What is the MySQL equivalent of PostgreSQL's EXPLAIN ANALYZE
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()
http://dev.mysql.com/doc/refman/5.0/en/explain.html
http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html
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.
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)