MySQL EXPLAIN ANALYZE

MySQL EXPLAIN ANALYZE runs a statement and shows what happened inside its execution plan. It combines optimizer estimates with measured timing, row counts, and loop counts. You can use this evidence to find slow plan steps and check whether MySQL estimated the data accurately.

This tutorial uses the current MySQL 8 syntax. Because EXPLAIN ANALYZE executes the statement, test write statements only in a safe environment or transaction.

Start with a representative SELECT query and realistic data. A plan measured against a tiny or unusual data set may not explain production performance.

EXPLAIN and EXPLAIN ANALYZE

Plain EXPLAIN asks the optimizer for its planned operations without running a SELECT. EXPLAIN ANALYZE executes the query and reports both the estimate and the actual work. The output uses tree format, with child operations indented below their parent.

Run EXPLAIN ANALYZE

Place the keywords before the statement you want to study. MySQL supports this analysis for SELECT statements and selected TABLE, multi-table UPDATE, and multi-table DELETE statements.

Syntax:

-- Execute the query and measure its plan
EXPLAIN ANALYZE
SELECT column_name
FROM table_name
WHERE condition;

Example:

-- Find paid orders for one customer
EXPLAIN ANALYZE
SELECT order_id, ordered_at, total_amount
FROM orders
WHERE customer_id = 1042
  AND status = 'PAID'
ORDER BY ordered_at DESC;

Read the Tree Output

Read the most deeply indented nodes first because they produce rows for their parent operations. Each node can show an estimated cost and row count, followed by actual timing, rows, and loops.

Typical output:

# Example plan shape; values depend on your data
-> Sort: orders.ordered_at DESC  (cost=42.10 rows=18)
    (actual time=2.105..2.108 rows=16 loops=1)
    -> Filter: (orders.status = 'PAID')  (cost=38.50 rows=18)
        (actual time=0.082..2.001 rows=16 loops=1)
        -> Table scan on orders  (cost=38.50 rows=360)
            (actual time=0.030..1.820 rows=360 loops=1)

• cost is the optimizer's relative estimate, not elapsed time.
• rows before the actual section is the estimated row count.
• actual time shows approximate time to return the first and last row for one loop.
• rows in the actual section is the average rows returned per loop.
• loops shows how many times MySQL executed that iterator.

Compare Estimates with Actual Rows

A large difference between estimated and actual rows can lead the optimizer toward an inefficient join order or access method. Check table statistics, data distribution, and predicates when estimates are consistently inaccurate.

Example:

-- Refresh table statistics before comparing the plan again
ANALYZE TABLE orders;

-- Measure the same query after statistics are updated
EXPLAIN ANALYZE
SELECT order_id
FROM orders
WHERE customer_id = 1042
  AND status = 'PAID';

Test a Useful Index

If the plan scans many rows to return only a few, a suitable index may reduce the work. Choose index columns from the query pattern instead of adding every filtered column automatically.

Example:

-- Support equality filters followed by the requested order
CREATE INDEX idx_orders_customer_status_date
ON orders (customer_id, status, ordered_at DESC);

-- Rerun the analysis and compare rows, loops, and timing
EXPLAIN ANALYZE
SELECT order_id, ordered_at, total_amount
FROM orders
WHERE customer_id = 1042
  AND status = 'PAID'
ORDER BY ordered_at DESC;

Look for a change from a table scan to an index lookup, fewer examined rows, and lower time in the relevant node. Measure the result rather than assuming the index helped.

Use EXPLAIN ANALYZE Safely

• Run it during a controlled test when the query is expensive.
• Use realistic parameters because different values can produce different plans.
• Do not compare only the final duration; inspect slow child nodes and repeated loops.
• Review indexes after schema or workload changes and remove indexes that add cost without value.
• Treat UPDATE and DELETE carefully because the analyzed statement performs the write.

You cannot request traditional or JSON output with EXPLAIN ANALYZE; its runtime details appear in tree output. You can use FORMAT=TREE explicitly, but it is already the analysis format.

Conclusion

MySQL EXPLAIN ANALYZE connects an execution plan with real measurements. Read the tree from its child nodes, compare estimated and actual rows, examine loops, and test focused changes such as updated statistics or a suitable index. Regular plan analysis helps you tune queries with evidence instead of guesswork.