Reading a MySQL Plan: EXPLAIN ANALYZE and FORMAT=TREE on InnoDB
MySQL 8.0.18 and later, including 8.4 LTS and 9.x, have EXPLAIN ANALYZE and EXPLAIN FORMAT=TREE, which print a plan tree with estimated and actual rows, loops and timings much like PostgreSQL's. The habits transfer directly: read inside-out, compare estimated to actual, multiply by loops. The differences worth knowing are that MySQL has no merge join, gained hash joins only in 8.0.18, and reports cost in its own units, so the same SQL can legitimately get a different shape of plan on the two engines.
Questions this Concept answers
- Why can the same SQL over the same data legitimately produce a differently shaped plan on MySQL than on PostgreSQL?
15.8.2Â EXPLAIN Statement
This is the MySQL manual page for the EXPLAIN statement, the command that shows how the database plans to run a query. It explains the output formats -- the old table format, JSON, and the tree forma…