Menu

MariaDB EXPLAIN FORMAT=JSON and ANALYZE FORMAT=JSON

Learn how MariaDB JSON plans show join order, access types, indexes, and row estimates, and how ANALYZE adds actual runtime statistics by executing the statement.

Posted on By
On this page

MariaDB EXPLAIN FORMAT=JSON returns the optimizer’s estimated plan as a JSON document. It shows how MariaDB expects to access tables and join them, along with details such as access type, candidate keys, estimated rows, and attached conditions.

ANALYZE FORMAT=JSON returns a similar plan with runtime measurements added. It also executes the statement to collect those measurements. Use it only when running the statement is safe and intended, especially for UPDATE or DELETE statements.

Get a JSON execution plan

Prefix a query with EXPLAIN FORMAT=JSON:

EXPLAIN FORMAT=JSON
SELECT order_id, created_at
FROM orders
WHERE customer_id = 42;

MariaDB returns one row with a JSON value in the EXPLAIN column. Copy the JSON object itself when using a parser or plan visualizer; terminal borders and the EXPLAIN: label are not part of the JSON.

A plan contains one or more table-access nodes. For example, a node may include fields like these:

{
  "table_name": "orders",
  "access_type": "range",
  "possible_keys": ["idx_customer_id"],
  "key": "idx_customer_id",
  "rows": 12,
  "filtered": 100,
  "attached_condition": "orders.customer_id = 42"
}

The exact fields depend on the query and server version. In a join, table nodes are nested under structures such as nested_loop; MariaDB’s JSON structure also differs from MySQL’s, so do not assume a MySQL parser can interpret every MariaDB plan field.

Read common plan fields

  • table_name identifies the table or derived table represented by a node.
  • access_type describes how MariaDB expects to find rows. ALL means a full table scan; it can be reasonable for a small table or a query that reads much of the table.
  • possible_keys lists candidate indexes. key is the index selected for that access, or NULL when no key is used.
  • rows is an estimate of rows examined for a lookup. It is an optimizer estimate, not a measured row count.
  • filtered estimates the percentage of rows that remain after the condition is applied.
  • attached_condition shows a condition associated with the table access. MariaDB may simplify or rewrite the original expression.

Use these fields to understand the chosen plan, not to infer that an index is always required. Consider table size, selectivity, data distribution, and the rest of the plan before changing indexes.

Compare estimates with actual execution

ANALYZE FORMAT=JSON executes the statement and adds actual values such as:

  • r_rows: average rows read by a node.
  • r_filtered: the observed percentage of rows remaining after a condition.
  • r_loops: how many times a plan node ran.
  • r_total_time_ms: total time spent in the node, including its child nodes when present.

For an UPDATE or DELETE, ANALYZE FORMAT=JSON makes the changes. For a SELECT, it runs the query and discards its result set. Never run it against a write statement unless the write is intentional and safe.

Paste either JSON output into SQLiz’s browser-based MySQL and MariaDB EXPLAIN plan visualizer to inspect table paths, selected keys, estimates, and actual statistics. The visualizer parses the JSON locally; it does not connect to a database or execute the query.

For the full syntax and behavior, see MariaDB’s official EXPLAIN, EXPLAIN FORMAT=JSON, and ANALYZE FORMAT=JSON references.