How to View Execution Plan in MariaDB

Galaxy Glossary

How do I view and interpret an execution plan in MariaDB?

EXPLAIN and EXPLAIN ANALYZE reveal how MariaDB executes a query so you can optimize indexes, joins, and filtering.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why inspect an execution plan in MariaDB?

Execution plans show table access order, join algorithms, index usage, and estimated vs. actual costs, letting you rewrite or re-index queries for speed.

Which commands display the execution plan?

Use EXPLAIN for estimates and EXPLAIN ANALYZE for runtime statistics. Both work on SELECT, DELETE, UPDATE, and INSERT ...SELECT.

How do I read the key columns?

id shows execution order, select_type shows query part, type indicates join strategy, key is the chosen index, rows is rows examined, and filtered is percentage passed to next step.

When should I use EXPLAIN ANALYZE?

Run it on slow queries in staging to compare actual_time and rows against estimates.Large gaps signal outdated statistics or missing indexes.

Example: locating a missing composite index

If type shows ALL and rows is high for Orders, create an index on (customer_id, order_date) to speed lookups by customer and date range.

Best practices for faster plans

Filter early, project only needed columns, use covering indexes, avoid functions on indexed columns, and keep statistics current with ANALYZE TABLE.

How to store and compare plans?

Save JSON plans to a version-control repo: EXPLAIN FORMAT=JSON \G.Diff them after schema or query changes to confirm improvements.

Can I visualize the plan?

Paste the JSON output into MariaDB’s online Visual EXPLAIN tool or compatible IDEs like Galaxy to get a graphical tree.

Why How to View Execution Plan in MariaDB is important

How to View Execution Plan in MariaDB Example Usage


-- Identify index usage when summarizing January revenue per customer
EXPLAIN ANALYZE FORMAT=JSON
SELECT c.name,
       SUM(o.total_amount) AS jan_revenue
FROM Customers c
JOIN Orders    o ON o.customer_id = c.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY c.id
ORDER BY jan_revenue DESC;

How to View Execution Plan in MariaDB Syntax


EXPLAIN [EXTENDED | PARTITIONS | FORMAT={TRADITIONAL|JSON}]
    SELECT *
    FROM Orders o
    JOIN Customers c ON c.id = o.customer_id
    WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31';

EXPLAIN ANALYZE [FORMAT=JSON]
    SELECT p.name, SUM(oi.quantity) AS sold
    FROM OrderItems oi
    JOIN Products p ON p.id = oi.product_id
    GROUP BY p.id
    ORDER BY sold DESC
    LIMIT 5;

Common Mistakes

Relying on EXPLAIN estimates alone. Fix: run EXPLAIN ANALYZE to capture actual row counts and times before acting.
Ignoring the "type" column when it shows ALL (full table scan). Fix: add or adjust indexes so the type becomes ref or range.