EXPLAIN, ANALYZE, FORMAT=JSON, Runtime Statistics, and Reading Plans

Learning outcomes

A developer posts an EXPLAIN screenshot and concludes that a query “reads 8 rows.” The rows column actually reports an estimate. Another developer runs ANALYZE against a production DELETE because it “sounds like a read-only explain command.” In MariaDB that is much more serious: ANALYZE executes the statement and then reports plan information augmented with runtime counters. This lesson builds a precise evidence ladder so plan inspection never becomes accidental data modification.

1. The four core plan surfaces

USE servicehub_optimizer_lab;EXPLAINSELECT * FROM work_ordersWHERE status='escalated' AND region_code='BAK';EXPLAIN FORMAT=JSONSELECT * FROM work_ordersWHERE status='escalated' AND region_code='BAK';ANALYZE FORMAT=JSONSELECT * FROM work_ordersWHERE status='escalated' AND region_code='BAK';

The output fields are evidence from different phases. Estimated rows belongs to optimization. Runtime r_rows shows rows read on average per loop for a node, r_loops shows how many times the node ran, and r_filtered reports the percentage left after a condition. A large divergence between estimated and observed row flow is a clue that statistics, correlation, predicates or parameter distribution deserve investigation.

2. Read row flow, not just the chosen key

For a nested-loop join, an inner node can execute once per outer row. That is why r_loops matters. An inner lookup that reads one row can still perform substantial total work if it runs 50,000 times. Conversely, a one-time scan of a small dimension table may be cheaper than thousands of random probes. Plan reading should therefore multiply loops by rows and notice filtering at each stage.

ANALYZE FORMAT=JSONSELECT c.customer_id,c.tier,       SUM(w.total_cents) AS total_valueFROM customers cJOIN work_orders w ON w.customer_id=c.customer_idWHERE c.region_code='BAK'  AND w.status IN ('open','escalated')GROUP BY c.customer_id,c.tierHAVING SUM(w.total_cents) > 20000ORDER BY total_value DESCLIMIT 20;

Start at the outer operation and follow nested row producers. Identify where sorting, temporary tables, grouping, materialization or repeated lookups appear. Then compare estimate and runtime. Do not reduce analysis to “key X was used.” The expensive work may happen after the access path—for example, a poor filter estimate can create many downstream loops even when every lookup uses an index.

3. ANALYZE FORMAT=JSON exposes more than row counts

Current MariaDB documentation describes runtime fields such as r_rows, r_filtered and r_loops. Newer releases also expose timing and engine statistics in applicable nodes; for example, recent versions can report InnoDB page-access/update counters. These fields are valuable when present, but they are version-sensitive diagnostics rather than a stable cross-vendor API contract.

SELECT VERSION() AS server_version,       @@version_comment AS build,       @@optimizer_switch AS optimizer_switch\GANALYZE FORMAT=JSONSELECT work_order_id,total_centsFROM work_ordersWHERE customer_id=120ORDER BY scheduled_at DESCLIMIT 10;

Store the SQL fingerprint, representative parameters, server build, optimizer switches, schema/index DDL and plan JSON with the performance incident. Without that context, a plan pasted into a ticket may be impossible to reproduce after statistics, data or a server patch changes.

4. Deliberately wrong: ANALYZE a destructive statement on live data

-- Plain EXPLAIN is the safe first choice for a write plan:EXPLAIN DELETE FROM work_order_eventsWHERE created_at < '2026-02-01';-- ANALYZE DELETE WOULD EXECUTE THE DELETE.-- If you need runtime write evidence, use a disposable clone or a verified test transaction.START TRANSACTION;SELECT COUNT(*) AS before_rows FROM work_order_events;ANALYZE DELETE FROM work_order_eventsWHERE created_at < '2026-02-01';SELECT COUNT(*) AS after_analyze_rows FROM work_order_events;ROLLBACK;SELECT COUNT(*) AS restored_rows FROM work_order_events;

The lab is intentionally explicit because wording can mislead: ANALYZE DELETE is not “analyze the DELETE without doing it.” It runs the DELETE. Even a transaction wrapper is not a universal safety device—DDL can commit implicitly, nontransactional engines do not roll back like InnoDB, triggers or external effects may exist, and locks can affect concurrent sessions. In production, prefer a restored copy or representative staging dataset for destructive runtime analysis.

5. Wrong conclusion: one ANALYZE run proves the query is fixed

Runtime plan output measures one execution under one cache state, one concurrency level and one parameter set. It does not prove p95 or p99 latency under load. A cold-cache run and a warm-cache run can differ. A parameter that selects one row and another that selects half the table can need different plans. Lock waits and I/O contention may not appear in a quiet single-session test.

Capture for each run:- server version/build and optimizer_switch- exact schema/index definitions- SQL fingerprint and representative bind values- dataset row counts and skew summary- EXPLAIN FORMAT=JSON- ANALYZE FORMAT=JSON (read-only or safe disposable workload)- cache/warmup method- concurrency level and latency percentiles- before/after change identifier- rollback criterion

The purpose of ANALYZE is to falsify optimizer assumptions. If estimated rows and runtime rows disagree sharply, investigate statistics or correlation. If row flow matches but time is high, investigate I/O, CPU, locks, sorting, temporary work or storage-engine behavior. If both improve after a change, repeat under representative concurrency before calling the regression solved.

6. A practical plan-reading checklist for incidents

When a plan is large, reading every JSON field in order is inefficient. Start with the statement’s business requirement and follow the largest row-flow multipliers. For each node, ask four questions: how many times did it execute, how many rows did each execution read, how many survived its condition, and what extra work such as sorting, temporary materialization, grouping, or base-row lookup followed. This method keeps attention on cumulative work rather than decorative plan detail.

For incident comparison, keep the same representative parameter set. A plan for status='escalated' cannot automatically explain the behavior of status='closed'. Parameter-class discipline prevents the common mistake of comparing two executions whose selectivity is fundamentally different.

7. When not to use ANALYZE

Do not use executing analysis when the statement is destructive, expensive enough to endanger a production SLO, or dependent on external side effects you cannot roll back. Plain EXPLAIN, a restored production snapshot, a traffic-replay environment, or a reduced representative dataset may provide a safer next step. Also remember that the act of measurement can perturb cache and concurrency. Diagnostic safety is part of query tuning, not an administrative afterthought.

8. Verification and bridge

Lesson 3 now focuses on the optimizer inputs most responsible for those estimate errors: InnoDB statistics, engine-independent statistics and histograms.

Authoritative references

• MariaDB Documentation — ANALYZE FORMAT=JSON
• MariaDB Documentation — EXPLAIN
• MariaDB Documentation — Optimizer Trace
• MariaDB Documentation — Query Optimizer