#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Science

MySQL EXPLAIN Statement: Analyze Query Execution Plans

Put the word EXPLAIN in front of a SELECT, UPDATE, DELETE or INSERT ... SELECT and MySQL shows the plan it would use to run the query: which tables it reads, in what order, through which index, and roughly how many rows it expects to touch. Plain EXPLAIN does not run the query, so it is safe on a slow statement. This page builds a small test database and reads real plans from MySQL 8.0.

Test data

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name  VARCHAR(40),
  city  VARCHAR(20),
  email VARCHAR(60) UNIQUE
);
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date  DATE NOT NULL,
  amount      DECIMAL(10,2),
  status      VARCHAR(10),
  KEY idx_cust (customer_id)
);

SET SESSION cte_max_recursion_depth = 100000;
INSERT INTO customers
WITH RECURSIVE n AS (SELECT 1 i UNION ALL SELECT i+1 FROM n WHERE i < 2000)
SELECT i, CONCAT('Customer ', i),
       ELT(1 + MOD(i,4), 'Noida','Bengaluru','Chennai','Hyderabad'),
       CONCAT('c', i, '@example.com')
FROM n;

INSERT INTO orders
WITH RECURSIVE n AS (SELECT 1 i UNION ALL SELECT i+1 FROM n WHERE i < 20000)
SELECT i, 1 + MOD(i*13, 2000), DATE('2025-01-01') + INTERVAL MOD(i,365) DAY,
       200 + MOD(i*31, 5000), ELT(1 + MOD(i,3), 'paid','pending','refunded')
FROM n;
ANALYZE TABLE customers, orders;

The columns, in the order you should read them

ColumnWhat it tells you
tableWhich table (or alias) this row of the plan reads. One row per table access, listed in join order.
typeHow the table is accessed. The most important column; see the next table.
possible_keysIndexes MySQL considered. NULL means no index fits the conditions.
keyThe index it actually chose. NULL with a large rows value is a warning sign.
key_lenBytes of the index used. On a composite index, tells you how many columns were used.
refWhat the index is compared with: const, or a column from an earlier table.
rowsEstimated rows examined for this step (an estimate from index statistics, not a count).
filteredEstimated percentage of those rows that survive the remaining conditions.
ExtraNotes such as Using index, Using where, Using filesort, Using temporary.

Access types from best to worst, with real output

EXPLAIN SELECT * FROM customers WHERE customer_id = 42;           -- 1
EXPLAIN SELECT order_id, amount FROM orders WHERE customer_id = 42;  -- 2
EXPLAIN SELECT order_id FROM orders WHERE order_id BETWEEN 100 AND 200; -- 3
EXPLAIN SELECT customer_id FROM orders;                            -- 4
EXPLAIN SELECT * FROM orders WHERE status = 'pending';             -- 5
#typekeyrefrowsExtra
1constPRIMARYconst1NULL
2refidx_custconst10NULL
3rangePRIMARYNULL101Using where; Using index
4indexidx_custNULL20178Using index
5ALLNULLNULL20178Using where

Notice query 5: filtered shows 10.00, but a third of the orders are actually pending. Without an index or histogram on status, MySQL simply guesses. After CREATE INDEX idx_status ON orders (status); the same query becomes type = ref with rows = 6667, which is close to the real 6,667. (In MySQL 8 you can also give the optimizer better estimates without an index: ANALYZE TABLE orders UPDATE HISTOGRAM ON status;.)

Reading a join plan

EXPLAIN
SELECT c.city, SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.city
ORDER BY revenue DESC;
tabletypekeyrefrowsfilteredExtra
oALLNULLNULL2017810.00Using where; Using temporary; Using filesort
ceq_refPRIMARYexpl.o.customer_id1100.00NULL

Read it top to bottom: MySQL scans every order, keeps the paid ones, and for each one fetches exactly one customer by primary key (eq_ref). The ref value expl.o.customer_id is database.alias.column; expl was the test database's name. Using temporary; Using filesort comes from the GROUP BY on another table's column followed by sorting on a computed total. With only four cities that is cheap; it becomes expensive when the grouped result is large.

A rough cost estimate for a join is the product of the rows values down the plan (here 20178 × 1).

EXPLAIN FORMAT=TREE and EXPLAIN ANALYZE (MySQL 8)

The tree format shows the same plan as nested steps, which is easier to follow for joins and subqueries:

-> Sort: revenue DESC
    -> Table scan on <temporary>
        -> Aggregate using temporary table
            -> Nested loop inner join  (cost=2748 rows=2018)
                -> Filter: (o.`status` = 'paid')  (cost=2042 rows=2018)
                    -> Table scan on o  (cost=2042 rows=20178)
                -> Single-row index lookup on c using PRIMARY (customer_id=o.customer_id)  (cost=0.25 rows=1)

EXPLAIN ANALYZE (8.0.18 and later) actually runs the query and adds real timings and real row counts next to the estimates:

-> Index lookup on orders using idx_cust (customer_id=42)  (cost=3.5 rows=10)
   (actual time=0.0587..0.0613 rows=10 loops=1)

The times will be different on your machine. What matters is comparing estimated rows with actual rows: a large gap means the optimizer is working from bad statistics. Because it executes the statement, do not run EXPLAIN ANALYZE on a heavy query on a busy production server.

Common mistakes when reading plans

For worked fixes based on these plans, read how to optimize MySQL queries and MySQL indexes explained. The clustered-index behaviour behind Using index is covered in clustered vs non-clustered indexes, and finding which queries to explain in the first place is covered in how to find and fix slow queries.

Launch Your Data Science Career

Learn MySQL, Python, Machine Learning and AI with Uncodemy's Data Science Training in Noida — hands-on projects, industry mentors and Dedicated Placement Support.

Enroll in Data Science Course →