New JSON Format for EXPLAIN and EXPLAIN ANALYZE in MySQL 8.3+

MySQL provides two powerful tools for analyzing query plans: EXPLAIN (which shows the optimizer‑chosen plan without executing the query) and EXPLAIN ANALYZE (which actually runs the query and reports timing and row‑count statistics).

The output can be rendered as a traditional table, a tree, or JSON. The original JSON format was designed for the old iterator‑less planner and therefore could not faithfully represent the iterator structure required by EXPLAIN ANALYZE.

In MySQL 8.3 Community Edition a new JSON format was added. It can be switched with the system variable explain_json_format_version={1,2} . Version 2 reflects the iterator‑based plan structure and matches the tree format, exposing each iterator as a JSON object that can be queried with JSON functions or accessed via EXPLAIN INTO .

Example usage (setting version 1):

mysql> SET explain_json_format_version=1;
mysql> EXPLAIN FORMAT=JSON SELECT name, quantity FROM orders JOIN items i ON item_id = i.id WHERE quantity > 1000;

Example usage (setting version 2, which is the new format):

mysql> SET explain_json_format_version=2;
mysql> EXPLAIN FORMAT=JSON SELECT name, quantity FROM orders JOIN items i ON item_id = i.id WHERE quantity > 1000;

The new JSON format mirrors the tree output: the top‑level iterator appears as the top‑level JSON object, child iterators are listed under the inputs field, and the operation description is stored in the operation field. Fields such as table_name now contain the base table name, while an alias field is added when a table alias is used.

When combined with EXPLAIN ANALYZE , the JSON output includes additional actual_* fields (e.g., actual_rows , actual_first_row_ms , actual_last_row_ms , actual_loops ) that capture real execution metrics, making programmatic performance analysis straightforward.

This new format is available in MySQL 8.3 Community, MySQL 8.4 LTS, MySQL 9.x, and in HeatWave MySQL on OCI, AWS, and Azure. It enables detailed query analysis locally or on the server using EXPLAIN INTO , and eliminates the need to manually parse tree‑format output.