Author: Sergey Petrunia

SHOW ANALYZE

If you ever had to do query performance troubleshooting with MariaDB, you should be familiar with MariaDB’s ANALYZE for statements feature. It does what EXPLAIN ANALYZE does in some other database systems: ANALYZE query runs the query and produces EXPLAIN output, amended with the data from the query execution:

There is also the ANALYZE FORMAT=JSON variant which provides much more information: it produces EXPLAIN FORMAT=JSON output, amended with observed runtime statistics. The statistics fields start with “r_”:

• “r_loops” shows how many times each query plan element was executed
• “r_rows” shows how many rows it has produced, on average
• “r_total_time_ms” shows how much time each query plan element took