ANALYZE FORMAT=JSON is a mix of the EXPLAIN FORMAT=JSON and ANALYZE statement
features. The ANALYZE FORMAT=JSON $statement will execute $statement, and then
print the output of EXPLAIN FORMAT=JSON, amended with data from the 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 not available in the regular 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, in milliseconds, 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.

SHOW ANALYZE FORMAT=JSON
------------------------

MariaDB starting with 10.9
--------------------------
SHOW ANALYZE FORMAT=JSON for <connection_id> extends ANALYZE [FORMAT=JSON]
<select> to allow one to analyze a query currently running in another
connection.

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/