ANALYZE FORMAT=JSON is a mix of the EXPLAIN FORMAT=JSON and
ANALYZE statement features. ANALYZE FORMAT=JSON $statement
will execute $statement, and then print the output of
EXPLAIN FORMAT=JSON, amended with the data from query
execution.
 
Basic Execution Data
 
You can get the following also from tabular ANALYZE
statement form:
r_rows is provided for any node that reads rows. It shows
how many rows were read, on average 
r_filtered is provided whenever there is a condition that is
checked. It shows the percentage of rows left after checking
the condition.
 
Advanced Execution Data
 
The most important data that is not available in tabula
ANALYZE statement are:
r_loops field. This shows how many times the node was
executed. Most query plan elements have this field.
r_total_time_ms field. It shows how much time in total was
spent executing this node. If the node has subnodes, their
execution time is included.
r_buffer_size field. Query plan nodes that make use of
buffers report the size of buffer that was was used.
 
Data About Individual Query Plan Nodes
 
filesort node reports whether sorting was done with LIMIT n
parameter, and how many rows were in the sort result. 
block-nl-join node has r_loops field, which allows to tell
whether Using join buffer was efficient 
range-checked-for-each-record reports counters that show the
result of the check. 
expression-cache is used for subqueries, and it reports how
many times the cache was used, and what cache hit ratio was.
union_result node has r_rows so one can see how many rows
were produced after UNION operation
and so forth
 
Use Cases
 
See Examples of ANALYZE FORMAT=JSON.
 

 
 
 
 
 
 
 

URL: https://mariadb.com/kb/en/analyze-format-json/