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_rowsis provided for any node that reads rows. It shows how many rows were read, on average
r_filteredis 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_loopsfield. This shows how many times the node was executed. Most query plan elements have this field.
r_total_time_msfield. It shows how much time in total was spent executing this node. If the node has subnodes, their execution time is included.
r_buffer_sizefield. 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>
ANALYZE [FORMAT=JSON] <select> to allow one to analyze a query currently running in another connection.
Data About Individual Query Plan Nodes
filesortnode reports whether sorting was done with
LIMIT nparameter, and how many rows were in the sort result.
r_loopsfield, which allows to tell whether
Using join bufferwas efficient
range-checked-for-each-recordreports counters that show the result of the check.
expression-cacheis used for subqueries, and it reports how many times the cache was used, and what cache hit ratio was.
r_rowsso one can see how many rows were produced after UNION operation
- and so forth