How to use MariaDB ANALYZE safely

Start with EXPLAIN

First inspect the estimated plan without running the query:

EXPLAIN
SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Review the access type, possible and selected keys, estimated rows, and Extra details. Estimates are not execution measurements, so a plan that looks plausible may still read more rows than expected.

Run ANALYZE only when appropriate

When safe, run the same SELECT through ANALYZE:

ANALYZE
SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

In tabular output, MariaDB adds runtime fields such as r_rows and r_filtered where applicable. JSON output can expose further fields, including loop information, depending on version. Compare actual and estimated row counts to find where assumptions diverge.

Read the official ANALYZE and EXPLAIN reference and the versioned ANALYZE FORMAT=JSON documentation.

Execution safety

ANALYZE does not merely estimate: it executes the statement. A SELECT can still consume substantial CPU, memory, I/O, or return sensitive data. Avoid testing an unbounded or expensive query on production without an operational review. Prefer a representative staging system, limit result size when that preserves the behavior you need to examine, and use a timeout/cancellation plan appropriate to your environment.

Do not use ANALYZE casually on INSERT, UPDATE, or DELETE. Syntax support does not remove the effects of executing a modifying statement.

How to use the output

Find the plan node where actual rows or filtering diverge from estimates. Check table/index statistics, data skew, predicates, join order, and whether the tested parameter reflects normal use. An estimate mismatch is a clue, not an instruction to add an index or refresh statistics blindly.

Verification steps

1. Capture server version, query, schema, indexes, and an EXPLAIN plan.
2. Choose safe representative data and run ANALYZE under controlled conditions.
3. Compare estimated rows, actual rows, loops, and elapsed time where reported.
4. Test one change at a time; verify result equivalence and compare repeated timings.

ANALYZE timings are observations from one run and can vary with cache state, concurrent load, and data. They are not a production latency guarantee.

Sources

• MariaDB: ANALYZE and EXPLAIN statements
• MariaDB: ANALYZE FORMAT=JSON
• MariaDB: EXPLAIN