Understanding MySQL EXPLAIN Output Formats and Using JSON for Detailed Query Cost Analysis

Previous articles introduced the EXPLAIN command for obtaining a SQL statement's execution plan and highlighted common questions such as index usage, file sorting, temporary tables, optimizer rewrites, and join algorithm verification.

Because traditional EXPLAIN output can be hard to read and lacks execution cost data, the FROMAT option allows selecting different output formats, each with unique content to compensate for the limitations of the default format.

EXPLAIN Output Formats

MySQL currently supports three EXPLAIN output formats:

EXPLAIN FORMAT=TRADITIONAL – the default tabular format with an EXTRA column for additional hints.

EXPLAIN FORMAT=JSON – outputs the execution plan as JSON, offering clearer and more detailed information.

EXPLAIN FORMAT=TREE – presents the plan in a tree structure for better visual hierarchy.

The remainder of this article focuses on interpreting the JSON format.

EXPLAIN FORMAT=JSON

The JSON format provides detailed data such as query cost, involved tables and columns, sorting usage, index usage, rows scanned, and granular cost breakdown (CPU, I/O, data read per join, etc.).

Example: JSON Execution Plan Output

mysql:ytt>desc format=json  select count(log_date) from t2 group by log_date\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {"query_cost": "40211.75"},
    "grouping_operation": {"using_filesort": false, "table": {"table_name": "t2", "access_type": "index", "possible_keys": ["idx_log_date"], "key": "idx_log_date", "used_key_parts": ["log_date"], "key_length": "4", "rows_examined_per_scan": 398830, "rows_produced_per_join": 398830, "filtered": "100.00", "using_index": true, "cost_info": {"read_cost": "328.75", "eval_cost": "39883.00", "prefix_cost": "40211.75", "data_read_per_join": "316M"}, "used_columns": ["id", "log_date"]}}}}
1 row in set, 1 warning (0.00 sec)

How to Quickly Assess SQL Execution Cost?

Traditional plans require multiple comparisons, while the JSON format includes cost information that can be directly compared to evaluate optimization effectiveness.

Example comparison:

SQL1: select count(*) from t1 where r1 in (select r1 from t1);
SQL2: select count(*) from t1 where 1;

Using the pager to filter query_cost shows that SQL1 has a lower cost (199210.09) than SQL2 (781454.78), confirming that SQL1 is more efficient.

Execution time also reflects this expectation.

mysql:ytt>select count(*) from t1;   -- 0.26 sec
mysql:ytt>select count(*) from t1 where r1 in (select r1 from t1);   -- 1.89 sec

What Additional Data Does JSON Provide?

For a two‑table join query (SQL3), the JSON plan reveals:

The join uses a nested_loop algorithm.

Total query cost is extremely high (6575362199.56).

Data read: outer table ~77 MB, inner table ~1 TB.

Only the column r1 is accessed from both tables.

These details make it clear that the query performs poorly.

Optimization Suggestions for SQL3

Discuss with business stakeholders to add additional join conditions or filtering predicates.

Consider replacing the join key with a primary key or a unique index to improve selectivity.

The article concludes with links to related resources, previous posts, and community information.