Analyze MariaDB Queries with EXPLAIN and ANALYZE Safely

Searches for MariaDB EXPLAIN ANALYZE often begin with a slow-query complaint and end with a syntax surprise. MariaDB uses EXPLAIN to show an estimated execution plan. To execute a statement and annotate that plan with observed runtime data, its native syntax is ANALYZE statement or ANALYZE FORMAT=JSON statement. The two commands answer different questions, and confusing them can be dangerous because ANALYZE UPDATE and ANALYZE DELETE really modify rows.

A plan is also not a simple good/bad report. Using filesort can be acceptable for 50 rows, an index lookup can be disastrous when repeated millions of times, and the optimizer's estimate can be reasonable overall while one skewed tenant produces a radically different workload. Useful diagnosis compares estimates with actual rows, loops, filtering, timing, pages, concurrency, and service impact.

This guide provides a production-minded workflow for MariaDB query plans. It explains tabular and JSON output, joins and access types, estimate errors, runtime analysis, long-running query inspection, optimizer trace, statistics, index experiments, plan changes across environments, and safe verification. Examples use read-only statements unless a warning explicitly says otherwise.

Use the right command for the question

MariaDB exposes several related tools:

MariaDB's ANALYZE statement should not be confused with ANALYZE TABLE, which collects or updates table statistics:

ANALYZE TABLE sales.orders;

That is a maintenance operation with different effects and resource cost. It does not execute a SELECT and return its runtime plan.

Establish a reproducible query case

Before reading a plan, record:

• Exact MariaDB version and edition.
• Full query shape and schema.
• Representative parameter values.
• Table definitions and indexes.
• Row counts and data distribution.
• Session optimizer and SQL-mode settings.
• Server role, hardware, and relevant configuration.
• Current latency, rows examined, call rate, and concurrency.

SELECT VERSION(), @@version_comment;

SELECT DATABASE(),
       @@SESSION.sql_mode,
       @@SESSION.optimizer_switch,
       @@SESSION.optimizer_use_condition_selectivity;

Retrieve definitions instead of reconstructing them from memory:

SHOW CREATE TABLE sales.ordersG
SHOW CREATE TABLE crm.customersG
SHOW INDEX FROM sales.orders;
SHOW INDEX FROM crm.customers;

Use the same character set, collation, time zone, and parameter types as the application. An implicit string-to-number conversion or collation conversion can change index eligibility. A CLI test with customer_id=481 may not represent an application binding '481' through a driver.

Data skew matters. Test a high-volume tenant, a typical tenant, a low-volume tenant, existing and missing values, and boundary dates. One convenient value is not a workload.

Start with plain EXPLAIN

For a read query:

EXPLAIN
SELECT o.id, o.created_at, o.total_amount
FROM sales.orders AS o
WHERE o.customer_id = 481
  AND o.status = 'paid'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 100;

Plain EXPLAIN asks the optimizer to plan the statement but does not execute a normal SELECT. It is the safest first view on production. It does acquire metadata access and consumes optimization resources, so even EXPLAIN is not completely free during schema contention.

The main tabular columns are:

• id: select block identifier.
• select_type: simple, primary, subquery, derived, union, or related role.
• table: table or materialized result accessed at that step.
• type: access method.
• possible_keys: indexes that might be usable.
• key: index actually chosen.
• key_len: bytes of the key prefix used.
• ref: value or earlier column used for lookup.
• rows: estimated rows read for the step.
• filtered: estimated percentage surviving attached conditions.
• Extra: notable operations and optimizations.

Do not read one column in isolation. The approximate number of rows passed from a step is:

estimated_output_rows = rows * filtered / 100

For a nested-loop join, work at an inner table multiplies by executions from outer rows. An inner rows=10 can be expensive if it runs 500,000 times.

Interpret access types as a spectrum

Common type values, roughly from narrow to broad access, include:

• const or system: at most one matching row known early.
• eq_ref: one unique-key row for each earlier-row combination.
• ref: non-unique index lookup for each earlier key.
• range: index range scan.
• index_merge: combines access from multiple indexes.
• index: scans an index rather than the full table.
• ALL: full table scan.

The ranking is guidance, not a verdict. ALL on 20 rows is fine. ref returning 100,000 rows per outer loop can be worse. index often means scanning most or all of an index; it does not mean selective lookup.

possible_keys listing an index does not mean it should be used. The optimizer may correctly choose a scan when a predicate matches most rows or when index lookups would cause expensive random table reads.

If key is NULL, ask:

• Is there a sargable predicate on a useful leading key part?
• Does an expression wrap the indexed column?
• Are compared data types and collations compatible?
• Is the predicate too unselective?
• Is the table so small that a scan is cheaper?
• Did join order make the index unusable at that point?

Read key and key_len without guessing

For a composite index:

CREATE INDEX ix_orders_customer_status_created
ON sales.orders (customer_id, status, created_at, id);

key_len helps show how much of the key can participate in lookup, but its byte count depends on data types, nullability, encoding, and internal representation. Do not convert bytes into “three columns used” from intuition alone.

EXPLAIN FORMAT=JSON exposes used_key_parts more directly on supported releases:

EXPLAIN FORMAT=JSON
SELECT o.id, o.created_at, o.total_amount
FROM sales.orders AS o
WHERE o.customer_id = 481
  AND o.status = 'paid'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 100;

Composite index order matters. Equality predicates commonly make good leading parts, followed by range or ordering columns according to the actual query. Once a range is used, later key parts may not further narrow the lookup in the way expected, though they can still help filtering, ordering, or covering depending on plan and version.

Never add this example index without checking existing indexes and write workload. Every secondary index adds storage, buffer-pool pressure, redo, backup time, and DML cost.

Understand the Extra column

Frequent values include:

Using where

MariaDB evaluates a condition after reading candidate rows. This is normal. Compare filtered and runtime r_filtered to see how much work the condition discards.

Using index

The required columns can be obtained from the index without reading the full table row, often called a covering access. It does not automatically imply selective access; the engine may still scan a large index.

Using index condition

Index Condition Pushdown evaluates part of a condition while scanning index entries, reducing full-row reads. Inspect rows and runtime page data rather than assuming it eliminates all waste.

Using temporary

The plan uses an internal temporary table, often for grouping, distinct results, unions, or materialization. Determine its size and whether it spills to disk. It is not automatically a bug.

Using filesort

MariaDB performs a sorting operation instead of returning rows in index order. “Filesort” may remain in memory. Count rows sorted, merge passes, and actual time.

Using join buffer

The join uses a buffering strategy because a direct indexed lookup is unavailable or another buffered algorithm was selected. In JSON runtime output, loop and timing data reveal whether it is effective.

Range checked for each record

MariaDB evaluates index choices for an inner table per outer-row combination. This can be costly and commonly points to complex predicates, missing composite indexes, or estimate uncertainty.

Treat Extra as navigation toward evidence, not a checklist where every phrase must disappear.

Use FORMAT=JSON for plan structure

Tabular rows flatten a tree. JSON better represents query blocks, nested loops, materialized derived tables, conditions, sorting, and used key parts:

EXPLAIN FORMAT=JSON
SELECT c.segment, SUM(o.total_amount) AS revenue
FROM crm.customers AS c
JOIN sales.orders AS o ON o.customer_id = c.id
WHERE c.region = 'EU'
  AND o.created_at >= '2026-08-01 00:00:00'
  AND o.created_at <  '2026-09-01 00:00:00'
GROUP BY c.segment
ORDER BY revenue DESC;

Save JSON as an artifact rather than relying on screenshots:

mariadb --batch --raw --skip-column-names -e "
EXPLAIN FORMAT=JSON
SELECT ...;
" > explain-before.json

jq . explain-before.json >/dev/null

Replace the placeholder query before execution; it is illustrative. Redact literals and schema details before sharing externally. The plan can reveal sensitive business structure even without result rows.

JSON field names and detail evolve by MariaDB release. A parser should tolerate absent optional members and record the server version with every plan.

Compare estimates with runtime ANALYZE

Tabular MariaDB ANALYZE executes the statement, discards a SELECT result set, and adds:

• r_rows: observed average rows read at that plan step.
• r_filtered: observed percentage left after the condition.

ANALYZE
SELECT o.id, o.created_at, o.total_amount
FROM sales.orders AS o
WHERE o.customer_id = 481
  AND o.status = 'paid'
ORDER BY o.created_at DESC, o.id DESC
LIMIT 100;

Compare pairs:

rows      versus r_rows
filtered  versus r_filtered

Large differences matter because the optimizer chooses join order, indexes, and algorithms from estimates. If it expects one inner match but reads 500 per outer loop, a nested-loop plan can explode.

r_rows can be NULL when a plan node was never executed. For example, an earlier table may produce zero rows, so later join steps are skipped. NULL does not mean instrumentation failed.

Critical safety warning

ANALYZE UPDATE and ANALYZE DELETE actually perform the update or deletion. They are not dry runs. ANALYZE INSERT and other explainable mutating statements must be treated the same way according to supported syntax.

Never paste production DML behind ANALYZE to “see what it would do.” Use plain EXPLAIN first, reproduce against a disposable restored database, or construct a semantically equivalent SELECT with great care. A transaction rollback is not a universal safety wrapper because locks, triggers, functions, external plugins, and operational impact can still occur.

Use ANALYZE FORMAT=JSON for timing and loops

ANALYZE FORMAT=JSON
SELECT c.segment, SUM(o.total_amount) AS revenue
FROM crm.customers AS c
JOIN sales.orders AS o ON o.customer_id = c.id
WHERE c.region = 'EU'
  AND o.created_at >= '2026-08-01 00:00:00'
  AND o.created_at <  '2026-09-01 00:00:00'
GROUP BY c.segment
ORDER BY revenue DESC;

This executes the SELECT and adds runtime fields to JSON. Important members include:

• r_loops: how many times a node executed.
• r_rows: average observed rows per execution for applicable nodes.
• r_filtered: observed percentage surviving a condition.
• r_table_time_ms: time spent in table access.
• r_other_time_ms: time in other work associated with the node.
• r_total_time_ms: runtime for larger nodes or query blocks.

From specified maintenance releases in the 10.6, 10.8, 10.9, 10.10, 10.11, and later families, InnoDB runtime JSON can also report fields such as pages accessed, pages updated, disk reads, read time, prefetch activity, and old row versions read. Feature-detect these fields.

For UPDATE and DELETE, top-level r_total_time_ms includes row modification time but does not include commit time. Do not compare it directly with application transaction latency and conclude durable commit is free.

Compute total inner work

Because r_rows is often an average per loop:

approximate_total_rows_read = r_loops * r_rows

An inner lookup with r_rows=20 and r_loops=100000 reads about two million rows. The average alone looks modest.

Find cardinality-estimation errors

A useful rough ratio is:

estimate_error_ratio = r_rows / rows

Handle zero and NULL values rather than dividing blindly. Ratios far above or below one indicate a candidate estimation problem, but the operational importance depends on loops and downstream choices.

Distinguish cold-cache and warm-cache plans

The optimizer plan may remain identical while runtime changes drastically with cache state. ANALYZE FORMAT=JSON page fields can show actual disk reads on supported releases. Host metrics can confirm storage work:

iostat -xz 1 10
vmstat 1 10

Do not clear the production buffer pool or operating-system caches to create a “cold” benchmark. That disrupts unrelated traffic and does not reproduce a realistic startup sequence.

Instead, use a staging clone, observe naturally cold and warm periods, or compare page-read counters. Document cache state alongside the runtime plan.

Also recognize that ANALYZE itself can warm pages. A second execution may be faster because the first loaded data, not because a change improved the query.

Inspect a currently running query

For a query that is still executing, obtain its connection ID:

SHOW FULL PROCESSLIST;

Then inspect its estimated plan from another authorized connection:

SHOW EXPLAIN FORMAT=JSON FOR 12345;

MariaDB also supports the MySQL-compatible form on applicable versions:

EXPLAIN FORMAT=JSON FOR CONNECTION 12345;

Permissions and version support apply. The target can finish or change state before inspection.

MariaDB 10.9 introduced runtime inspection of the active execution:

SHOW ANALYZE FORMAT=JSON FOR 12345;

This is valuable when waiting for a statement to complete just to obtain ANALYZE would be unacceptable. Runtime values are progress so far, not final totals. Capture a timestamp and, if safe, repeat to see which node advances.

Do not expose other users' SQL broadly. Process and plan privileges can reveal literals and schema information. Apply least privilege and incident access controls.

If the query threatens service health, diagnosis must not delay mitigation. Confirm the connection and use:

KILL QUERY 12345;

This interrupts the statement while leaving the connection where supported. Killing DML can trigger rollback, which may take substantial time and I/O. Never kill solely because type=ALL appears in a plan.

Diagnose row-estimate errors

When estimated and observed cardinalities differ substantially, investigate:

• Stale persistent statistics.
• Highly skewed or correlated columns.
• Predicates on expressions or functions.
• Data-type and collation conversions.
• Correlation between join keys and filters.
• Parameter values outside the typical distribution.
• Derived tables, subqueries, or complex OR conditions.
• Statistics sampling limitations.

Inspect table statistics and metadata supported by the version. A controlled refresh may help:

ANALYZE TABLE sales.orders;

This statement reads table/index data, updates optimizer statistics, and can consume resources. It can change plans for many queries, not just the target. Test and schedule it.

MariaDB supports engine-independent table statistics and histograms through version-specific ANALYZE TABLE options. Do not copy syntax across releases. Collect statistics only for columns where selectivity knowledge improves planning, and monitor plan changes across the workload.

If estimates are correct for typical values but wrong for a large tenant, the issue is skew, not necessarily stale data. Schema partitioning, tenant-aware query paths, summary tables, or separate workload handling may be more robust than forcing one global plan.

Read join order as repeated work

MariaDB commonly uses nested-loop joins. The first table produces rows; each later table is accessed for combinations produced so far.

Example:

EXPLAIN FORMAT=JSON
SELECT c.id, SUM(oi.quantity * oi.unit_price) AS value
FROM crm.customers AS c
JOIN sales.orders AS o ON o.customer_id = c.id
JOIN sales.order_items AS oi ON oi.order_id = o.id
WHERE c.region = 'EU'
  AND o.status = 'paid'
GROUP BY c.id;

Ask at every node:

• How many loops reach this node?
• How many rows are read per loop?
• How selective is the attached condition?
• Is the lookup using an equality or broad range?
• Are rows filtered before or after an expensive join?
• Does a later grouping discard most of the joined rows?

An index on orders(customer_id) may support the join, but (customer_id, status) may reduce rows earlier if the status predicate is selective. Whether that index is worthwhile depends on the full read/write workload.

Avoid forcing join order as the first fix. Hints can freeze a plan that works for today's data and fails after distribution changes. Repair statistics, predicates, and index design first; use hints only with documented evidence and upgrade tests.

Diagnose temporary tables and sorting

If a plan reports Using temporary or a JSON materialization node, inspect:

• Rows entering the operation.
• Row width and selected payload.
• Grouping and distinct keys.
• Whether filtering can happen earlier.
• Disk-temp counters for the statement.
• Memory limits and tmpdir pressure.

If it reports filesort, inspect sorted rows, limit behavior, merge passes, and runtime time. A composite index may provide order only when leading predicates and order directions align.

For stable pagination:

SELECT id, created_at, total_amount
FROM sales.orders
WHERE customer_id = 481
  AND (created_at, id) < ('2026-08-25 12:00:00', 9000000)
ORDER BY created_at DESC, id DESC
LIMIT 100;

This keyset form can avoid scanning and discarding a huge OFFSET, but it changes the API contract and requires a deterministic cursor.

Accept a bounded sort when an index would be large, seldom used, or expensive for writes. The plan should support a service objective, not achieve an aesthetically empty Extra column.

Use optimizer trace when the chosen plan is puzzling

EXPLAIN shows what was chosen. Optimizer trace helps explain alternatives, transformations, and cost decisions.

Enable it only in the diagnostic session:

SET SESSION optimizer_trace = 'enabled=on';
SET SESSION optimizer_trace_max_mem_size = 1048576;

Run the target read query safely, then retrieve the trace:

SELECT TRACE
FROM information_schema.OPTIMIZER_TRACEG

Each connection retains trace information for its last traced statement. Querying other tables can replace or affect what is available, so follow the version-specific sequence.

Disable it afterward:

SET SESSION optimizer_trace = 'enabled=off';

Trace can be large and can contain SQL literals. Protect it like a slow-log artifact. It adds diagnostic overhead and should not be globally enabled.

Look for considered access paths, estimated selectivity, rejected plans, join-order exploration, and transformations. Do not manually edit optimizer cost assumptions merely because one alternative has a lower-looking component; the cost model and units require release-specific expertise.

Test a candidate index responsibly

Start by inventorying indexes:

SHOW INDEX FROM sales.orders;

Design against a concrete query pattern. For:

WHERE customer_id = ?
  AND status = ?
ORDER BY created_at DESC, id DESC
LIMIT 100

a candidate is:

CREATE INDEX ix_orders_customer_status_created_id
ON sales.orders (customer_id, status, created_at, id);

Before production DDL:

1. Estimate index size and available disk.
2. Check overlap with existing indexes.
3. Measure DDL time and locking on production-sized staging data.
4. Observe redo, I/O, replica lag, and Galera effects.
5. Compare read plans for skewed values.
6. Benchmark insert, update, delete, backup, and recovery cost.
7. Define an abort and removal window.

Capture before and after:

EXPLAIN FORMAT=JSON SELECT ...;
ANALYZE FORMAT=JSON SELECT ...;

The second command executes the query; substitute a safe full SELECT. A lower estimated cost is insufficient if actual total workload performance regresses.

Rewrite predicates for usable access

A function on an indexed column often prevents a normal range lookup:

WHERE DATE(created_at) = '2026-08-25'

Use a half-open range with correct time-zone semantics:

WHERE created_at >= '2026-08-25 00:00:00'
  AND created_at <  '2026-08-26 00:00:00'

Other frequent problems include:

• Leading-wildcard LIKE '%term'.
• Arithmetic on an indexed column.
• Implicit casts between numeric and string types.
• Different collations on join columns.
• OR branches that need different access paths.
• Non-deterministic expressions.

Do not rewrite semantics merely to make an index appear. Time zones, NULL behavior, case sensitivity, rounding, and duplicate handling need tests.

Avoid common plan-reading mistakes

“The query uses an index, so it is optimized”

An index scan may read millions of entries. Check access type, rows, loops, filtering, pages, and total time.

“Full scans are always bad”

A scan can be cheapest for a small table or unselective predicate. The problem is expensive repeated or broad work, not the word ALL.

“Using filesort means disk”

Filesort names the algorithmic path, not necessarily a file write. Use runtime and system counters.

“rows is the real count”

rows is an optimizer estimate. r_rows is observed average work from ANALYZE. Both need loops and filtering context.

“ANALYZE is read-only”

Only a SELECT is read-only by statement semantics. ANALYZE UPDATE and ANALYZE DELETE perform changes.

“The fastest single execution is the best plan”

Cache warmth, background traffic, parameter skew, and concurrency distort one run. Compare distributions and total workload cost.

“A hint fixes plan instability”

A hint may conceal stale statistics or skew and can age poorly. Monitor and retest it through upgrades and data growth.

Troubleshoot plan anomalies

EXPLAIN differs between staging and production

Compare MariaDB patch version, schema, indexes, row counts, statistics, collations, optimizer settings, data distribution, and session variables. A small staging dataset often produces a scan where production uses an index, or the reverse.

ANALYZE is much slower than the application query

Check cache state, parameter values, concurrency, result behavior, session settings, and whether instrumentation detail adds overhead. The application may use a prepared statement or a replica with different data.

Estimated rows are accurate but runtime is slow

Investigate pages read, storage latency, lock waits, CPU, sorting, row width, old versions, and result transfer. Cardinality is only one part of cost.

r_rows is NULL

The node may not have executed because an earlier node produced no matching rows. Inspect outer r_filtered, loops, and control flow.

EXPLAIN chooses no possible key

Check leading key parts, predicate form, casts, collations, selectivity, and whether the desired ordering conflicts with filtering. Do not force a nonexistent useful access path.

SHOW ANALYZE returns an error

Confirm MariaDB 10.9+, target connection state, privilege, connection ID, and supported statement type. The query may have finished between process-list capture and inspection.

A new index is ignored

It may be redundant, unselective, incompatible with leading predicates, more expensive than a scan, or based on stale statistics. Compare trace and actual runtime before forcing it.

Verify the final change end to end

Use a before/after matrix:

Run production-sized concurrency, not only serial executions. A plan with fewer reads can still cause lock contention; an index can accelerate SELECT while lowering write throughput.

Keep rollback practical. Query rewrites should be feature-flagged where possible. Index removal is DDL and may require its own maintenance window. Statistics changes can affect unrelated plans. Define thresholds and an owner before rollout.

Best practices checklist

• Use plain EXPLAIN as the first production-safe view.
• Record version, schema, session variables, and representative values.
• Read access type, rows, filtering, key parts, and Extra together.
• Multiply inner runtime rows by loops.
• Use JSON for plan trees and node timing.
• Remember that MariaDB ANALYZE executes the statement.
• Never run ANALYZE UPDATE or DELETE as a dry run.
• Use SHOW ANALYZE for active queries only on supported versions.
• Treat estimate errors as evidence to investigate statistics and skew.
• Test high-, normal-, low-, and zero-match parameter classes.
• Measure cache and storage state with runtime plans.
• Enable optimizer trace only in a controlled session.
• Evaluate index benefit across reads, writes, backups, and replicas.
• Save comparable before/after artifacts.
• Validate service latency and throughput, not just plan appearance.

FAQ

Does MariaDB support EXPLAIN ANALYZE?

MariaDB's native executed-plan syntax is ANALYZE statement or ANALYZE FORMAT=JSON statement. Plain EXPLAIN returns estimates without running a normal SELECT.

Is MariaDB ANALYZE safe on production?

It executes the target. A bounded SELECT may be acceptable after risk review, but an expensive SELECT can harm traffic and ANALYZE DML really changes data. Prefer staging first.

What is the difference between rows and r_rows?

rows is the optimizer's estimated rows per plan step. r_rows is the observed average rows read during execution. Interpret both with r_loops and filtering.

Is Using filesort always a problem?

No. It means results are sorted outside index order and may stay in memory. Judge sorted rows, runtime, merge passes, frequency, and index trade-offs.

Why does MariaDB ignore my new index?

The optimizer may estimate a scan is cheaper, the key may not match leading predicates, selectivity may be low, or statistics may be stale. Verify with JSON, runtime data, and trace.

How can I inspect a query that has not finished?

Use SHOW EXPLAIN FOR connection_id for its plan. On MariaDB 10.9+, SHOW ANALYZE FORMAT=JSON FOR connection_id adds runtime progress so far.

Does ANALYZE TABLE show a query plan?

No. ANALYZE TABLE collects table statistics. ANALYZE SELECT ... executes the SELECT and returns an observed query plan.

Should every full table scan get an index?

No. Scans can be optimal for small tables or unselective predicates. Add an index only when measured workload benefit exceeds write, storage, and operational cost.

Conclusion

Effective MariaDB EXPLAIN ANALYZE work is a comparison between what the optimizer expected and what the server actually did. Begin with a reproducible query, representative parameter classes, plain EXPLAIN, and a structured reading of access, rows, filtering, key parts, and join order. Move to JSON when the flattened table hides loops, materialization, or timing.

Use MariaDB's ANALYZE syntax only with full awareness that it executes the statement. Runtime rows, loops, page activity, and node time can reveal stale statistics, data skew, repeated inner work, and storage pressure that an estimated plan cannot. Verify every rewrite or index under realistic concurrency and include its write-side cost. A plan is successful when the service becomes faster and more stable, not merely when Using filesort disappears.

Suggested Internal Links

• Use the MariaDB Slow Query Log for Better Diagnosis
• Design Effective MariaDB Indexes Without Over-Indexing
• Tune MariaDB Temporary Tables and Sort Operations Safely
• Find and Fix MariaDB Lock Waits and Deadlocks
• Understand MariaDB Configuration Files and Precedence
• Size the MariaDB InnoDB Buffer Pool for Production

Suggested External Sources

• MariaDB ANALYZE statement
• MariaDB ANALYZE FORMAT=JSON
• MariaDB SHOW EXPLAIN
• MariaDB SHOW ANALYZE
• MariaDB optimizer trace overview
• MariaDB EXPLAIN documentation