How to View Execution Plan in MySQL

Galaxy Glossary

How do I view and interpret execution plans in MySQL?

EXPLAIN and EXPLAIN ANALYZE reveal MySQL’s execution plan, showing how the optimizer reads and joins tables so you can tune queries.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

What does EXPLAIN do in MySQL?

EXPLAIN runs the optimizer without executing the query and returns the plan it intends to use. Columns such as type, possible_keys, and rows highlight join strategy, index usage, and estimated work.

How do I get a basic execution plan?

Prefix your DML statement with EXPLAIN. For example: EXPLAIN SELECT * FROM Orders;. MySQL returns one row per table accessed, ordered by join sequence.

Why use EXPLAIN ANALYZE?

EXPLAIN ANALYZE both executes the query and measures actual timing, loops, and rows-read, letting you compare estimates with reality for precise tuning.

How can I read key columns quickly?

Start with type. Values range from system (best) to ALL (full scan). Check key for chosen index and rows for estimated row count. High row counts or ALL signal potential problems.

Can I get JSON output?

Yes. Use EXPLAIN FORMAT=JSON. The JSON object includes nested loops, cost estimates, and index condition details, which are easier to parse programmatically.

How does this help my ecommerce database?

Use EXPLAIN to verify that frequent sales reports filter by indexed columns (Orders.order_date) and join Orders to Customers via primary keys, avoiding costly scans that slow dashboards.

Best practices for faster plans

Create composite indexes that match WHERE and JOIN clauses, compare estimated rows to reality with EXPLAIN ANALYZE, and always analyze queries before shipping to production.

Why How to View Execution Plan in MySQL is important

How to View Execution Plan in MySQL Example Usage


EXPLAIN ANALYZE FORMAT=JSON
SELECT p.name,
       SUM(oi.quantity) AS units_sold,
       SUM(oi.quantity * p.price) AS revenue
FROM OrderItems oi
JOIN Products p   ON p.id = oi.product_id
JOIN Orders   o   ON o.id = oi.order_id
WHERE o.order_date >= '2024-01-01'
GROUP BY p.id
ORDER BY revenue DESC
LIMIT 5;

How to View Execution Plan in MySQL Syntax


EXPLAIN [ANALYZE] [VERBOSE] [FORMAT=TRADITIONAL|JSON]
    SELECT
        column_list
    FROM Orders
    JOIN Customers   ON Customers.id = Orders.customer_id
    LEFT JOIN OrderItems ON OrderItems.order_id = Orders.id
    WHERE Orders.order_date BETWEEN '2024-01-01' AND '2024-01-31'
      AND Customers.email LIKE '%@example.com%';

-- Key options
ANALYZE   Executes query and shows actual metrics
VERBOSE   Displays additional optimizer information (8.0.33+)
FORMAT    TRADITIONAL (default table layout) or JSON

Common Mistakes

Relying on EXPLAIN alone. EXPLAIN shows estimates; without ANALYZE you might miss mis-estimates that only appear when the query actually runs. Fix: use EXPLAIN ANALYZE before final tuning.
Ignoring the "type" column when it shows "ALL". Many see an index in the key column and assume it’s used. If type is ALL, the index was skipped. Fix: rewrite WHERE clause or add composite indexes so type becomes range, ref, or eq_ref.

Frequently Asked Questions (FAQs)

Does EXPLAIN lock tables?

No. EXPLAIN never locks or mutates data; ANALYZE does read data but still uses shared locks only.

Is EXPLAIN ANALYZE safe in production?

Yes for moderate queries. It runs the statement, so avoid on updates that modify data or extremely heavy reads during peak traffic.

How do I store plans for later analysis?

Save the JSON output to a file or table, or enable performance_schema and query events_statements_history.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.