Mariadb Explain

EXPLAIN in MariaDB

Last updated: 2026-07-20

This skill covers the delta between the EXPLAIN/ANALYZE/query-plan family most LLMs default to and MariaDB's actual grammar and runtime behavior, verified against sql/sql_yacc.yy, sql/sql_parse.cc, sql/sql_explain.cc, sql/sql_update.cc, and sql/sql_delete.cc in the MariaDB server source.

Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Features available across current LTS branches (10.6, 10.11, 11.4, 11.8) are shown without a "since" annotation; only newer-than-10.6 features carry one.

What LLMs Often Miss

The Statement Family — Estimate vs. Actual

MariaDB's plan-inspection surface has three tiers, all sharing the same tabular/JSON output shape:

EXPLAIN ANALYZE as its own two-keyword statement is retired: EXPLAIN and ANALYZE head two independent, non-combinable grammar productions. An agent asked to "profile this query" that reflexively emits EXPLAIN ANALYZE … will get a syntax error, not a plan. The correct rewrite is dropping the EXPLAIN and keeping only ANALYZE.

FORMAT=JSON

EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE col1 = 1;
ANALYZE FORMAT=JSON SELECT * FROM t1 WHERE col1 = 1;

Both return a single row with a single column holding a JSON document describing the query block, tables, access types, and (for ANALYZE FORMAT=JSON) execution statistics. MariaDB's JSON shape is its own design — not a port of any other engine's FORMAT=JSON, and not interchangeable with it field-for-field.

ANALYZE FORMAT=JSON adds, beyond the tabular r_rows/filtered/r_filtered:

• r_loops — how many times a plan node executed.
• r_total_time_ms — cumulative time spent in a node, including its subnodes. For UPDATE/DELETE, this includes making the row changes but excludes commit time.
• r_buffer_size — size of any buffer the node used (e.g., join buffer).
• r_engine_stats (InnoDB tables) — page-access counters; only non-zero members are printed.
• (since 11.5) r_index_rows and r_icp_filtered — separate the count of index tuples enumerated (before any check) from post-Index-Condition-Pushdown selectivity; and r_total_filtered — combined selectivity across ICP, Rowid Filtering, and the attached condition.

EXPLAIN EXTENDED + SHOW WARNINGS

EXPLAIN EXTENDED SELECT * FROM employees WHERE last_name = 'Marx';
SHOW WARNINGS;

EXTENDED is a live, non-deprecated modifier — it is not folded into the default tabular output. It does two things:

1. Adds the filtered column (percentage estimate of rows surviving the attached condition) to the tabular result.
2. Emits exactly one Note-level warning containing the query as the optimizer rewrote it (constant propagation, redundant-condition removal, etc.) — retrievable with a subsequent SHOW WARNINGS.

Both behaviors fire only for the tabular path; EXTENDED and FORMAT=JSON are alternatives, not composable — the JSON output already carries the filtered and attached_condition fields unconditionally. ANALYZE always includes the filtered column (and r_filtered) with or without EXTENDED.

SHOW EXPLAIN / SHOW ANALYZE FOR <connection_id>

SHOW PROCESSLIST;                      -- find the connection id
SHOW EXPLAIN FOR 7;                    -- what is connection 7 doing?
SHOW ANALYZE FOR 7;                    -- ...with runtime stats so far
SHOW EXPLAIN FORMAT=JSON FOR 7;        -- same, as JSON     (since 10.9)
SHOW ANALYZE FORMAT=JSON FOR 7;        -- same, as JSON     (since 10.9)
EXPLAIN FOR CONNECTION 7;              -- alias for SHOW EXPLAIN FOR 7 (since 10.9)

MariaDB-specific: there is no need to KILL and rerun a long query, or wait for it to finish, to see what it's doing. SHOW EXPLAIN snapshots the query plan of a statement actively running in another session; SHOW ANALYZE additionally reports the runtime statistics accumulated so far. Both require the same privilege as SHOW PROCESSLIST, and both error with ER_TARGET_NOT_EXPLAINABLE ("Target is not running an EXPLAINable command", error 1932) if the target connection isn't currently mid-plan (e.g., still opening tables) or isn't running an explainable statement at all.

SHOW ANALYZE picks up detailed timing (r_..._time_ms fields) only if the target connection is itself running ANALYZE; a plain SELECT being inspected this way reports row counts but not timings, since timing collection is opt-in via ANALYZE.

EXPLAIN for INSERT / UPDATE / DELETE / REPLACE

EXPLAIN UPDATE t1 SET c1 = c1 + 1 WHERE c2 > 100;
EXPLAIN DELETE FROM t1 WHERE c2 > 100;
EXPLAIN INSERT INTO t1 SELECT * FROM t2 WHERE c2 > 100;
ANALYZE DELETE FROM t1 WHERE c2 > 100;   -- actually deletes

All four DML forms are directly explainable. As with SELECT, EXPLAIN on any of these is a pure dry run: the server derives and prints the plan, then stops before touching data. ANALYZE on any of these executes for real; the only exception the optimizer takes is bailing out early (before running the write) when it can already prove the WHERE clause matches nothing.

EXPLAIN tbl_name — the DESCRIBE Synonym

EXPLAIN employees;      -- same as: DESCRIBE employees;  /  SHOW COLUMNS FROM employees;

This form has nothing to do with query plans. EXPLAIN/DESCRIBE/DESC are three spellings of the same keyword at the lexer level; followed directly by a table name (no SELECT/UPDATE/etc.), it lists that table's columns, types, keys, and defaults — identical to SHOW COLUMNS FROM.

Examples

-- Estimate only: no usable index, so it's a full table scan
EXPLAIN SELECT * FROM employees_example WHERE home_phone = '326-555-3492';
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | employees_example | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+------+-------------+-------------------+------+---------------+------+---------+------+------+-------------+

-- Estimate + actual: ANALYZE runs the query and adds r_rows / r_filtered
ANALYZE SELECT * FROM tbl1 WHERE key1 BETWEEN 10 AND 200 AND col1 LIKE 'foo%';
           id: 1
  select_type: SIMPLE
        table: tbl1
         type: range
possible_keys: key1
          key: key1
      key_len: 5
          ref: NULL
         rows: 181
       r_rows: 181
     filtered: 100.00
   r_filtered: 10.50
        Extra: Using index condition; Using where

-- Inspecting a query already running in another session (no need to wait for it)
SHOW PROCESSLIST;
SHOW EXPLAIN FOR 1;
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | tbl   | index | NULL          | a    | 5       | NULL | 1000107 | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+---------+-------------+

See Also

• mariadb-select — the queries whose plans EXPLAIN describes.
• mariadb-show — SHOW EXPLAIN/SHOW ANALYZE FOR, and other introspection statements.
• Canonical references on mariadb.com/docs:
  • https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/analyze-and-explain-statements/explain
  • https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/analyze-and-explain-statements/analyze-statement
  • https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/analyze-and-explain-statements/explain-format-json

This page is: Copyright © 2026 MariaDB. All rights reserved.