Skip to content
Tutorials Center
My learning

Topics

Lesson 45 of 75 ยท Indexes and Performance

EXPLAIN and EXPLAIN ANALYZE

Read MySQL query plans: the type, key, rows, filtered and Extra columns, FORMAT=TREE and JSON output, EXPLAIN ANALYZE actual times, and how join order is chosen.

When a query is slow, guessing is expensive. EXPLAIN asks the optimizer to show its plan: which tables it will read, in what order, using which index, and how many rows it expects to touch. It is the single most useful tool for query tuning, and reading it takes about ten minutes to learn.

This lesson explains each column of MySQL's tabular EXPLAIN, the tree and JSON formats, and EXPLAIN ANALYZE, which actually runs the query and compares the estimates with reality. The output of EXPLAIN comes from a MySQL server, so the MySQL outputs on this page are read-only examples shaped like real ones. The live examples run SQLite's EXPLAIN QUERY PLAN, which answers the same questions in plain sentences, so you can practice the habit of reading a plan.

Read a plan in five looks

type How rows are accessed. ALL is a full scan.
key Which index was really chosen.
rows Estimated rows examined.
filtered Share of those rows that pass the rest of WHERE.
1 / 2

Running EXPLAIN

Put EXPLAIN in front of a SELECT, UPDATE, DELETE or INSERT. Plain EXPLAIN does not run the statement. It only plans it, so it is safe on any query.

SQL
EXPLAIN
SELECT * FROM orders
WHERE customer_id = 7
ORDER BY placed_at DESC
LIMIT 20\G
TEXT
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ref
possible_keys: idx_customer_placed
          key: idx_customer_placed
      key_len: 8
          ref: const
         rows: 31
     filtered: 100.00
        Extra: Backward index scan

This output is illustrative: the numbers on your server will differ. The \G ending prints one column per line, as in Using the mysql Command-Line Client. Each row of the output describes one table access, and a query with joins or subqueries has several rows.

The columns that matter

id and select_type. id numbers each SELECT in the statement, and rows with the same id are joined together in the order listed. select_type is SIMPLE for a query without unions or subqueries, and values such as PRIMARY, SUBQUERY, DERIVED and UNION label the parts of complex ones.

table. The table or alias being read. For joins, the row order is the join order: the first row is the outer (driving) table, and each later row is looked up once per row from the earlier ones.

type: the access method. This is the most important column. From best to worst:

type Meaning
system, const At most one row, found by primary key or unique index with a constant.
eq_ref One row per row of the previous table, via a primary key or unique index in a join.
ref Several rows matching a value of a non-unique index.
range A range of an index (BETWEEN, <, >, IN, LIKE 'abc%').
index A full scan of an index. Better than ALL only because the index is smaller.
ALL A full table scan. Reads every row.

ALL on a large table is the usual red flag, particularly in the inner table of a join, where it repeats once per outer row.

possible_keys and key. possible_keys lists indexes the optimizer considered. key is the one it chose. If key is NULL, no index is used. If possible_keys is NULL, nothing usable exists, which tells you to create an index or fix the query so one applies (see Query Optimization Techniques).

key_len. The number of bytes of the index actually used. For a composite index it tells you how many columns are used. With INDEX (customer_id, placed_at) where customer_id is BIGINT NOT NULL (8 bytes) and placed_at is DATETIME (5 bytes, plus 1 if nullable), key_len: 8 means only customer_id is used for the lookup, and key_len: 13 means both. This is the quickest way to check the leftmost prefix rule from Designing Composite Indexes.

ref. What is compared to the index: const for a constant, or a column such as shop.c.id for a join.

rows. The optimizer's estimate of how many rows it must examine for this table access. It comes from index statistics, so it can be wrong (see How MySQL Indexes Work).

filtered. The estimated percentage of those rows that survive the rest of the WHERE conditions (the ones the index could not use). rows * filtered / 100 is the number of rows passed to the next table in a join. A low value, such as 5.00, means the access reads many rows and discards most, which suggests a better index.

Extra. Notes about how the work is done. The ones to recognize:

Extra Meaning
Using index Covering index: answered without reading table rows. Good.
Using where The server filters rows after reading them.
Using index condition The condition is checked inside the index before reading the row (index condition pushdown). Good.
Using filesort A sort step is needed, because no index delivers the ORDER BY order. It may be in memory or on disk.
Using temporary An internal temporary table is created, often for GROUP BY or DISTINCT.
Backward index scan The index is read in reverse to satisfy DESC.
Using join buffer (hash join) A join with no usable index uses a hash join (8.0.18+).
Impossible WHERE The condition can never be true, so no rows are read.

"Filesort" is a misleading name: it does not always touch disk. It means MySQL sorts rows itself instead of reading them in index order.

A join, read step by step

SQL
EXPLAIN
SELECT c.name, SUM(o.total)
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE c.country = 'DE'
GROUP BY c.id;
TEXT
+----+-------------+-------+------+---------------------+---------------------+---------+-----------+------+----------+-------+
| id | select_type | table | type | possible_keys       | key                 | key_len | ref       | rows | filtered | Extra |
+----+-------------+-------+------+---------------------+---------------------+---------+-----------+------+----------+-------+
|  1 | SIMPLE      | c     | ref  | idx_country         | idx_country         | 82      | const     |  120 |   100.00 | NULL  |
|  1 | SIMPLE      | o     | ref  | idx_customer_placed | idx_customer_placed | 8       | shop.c.id |   30 |   100.00 | NULL  |
+----+-------------+-------+------+---------------------+---------------------+---------+-----------+------+----------+-------+

Reading it: MySQL starts with c (customers) because country = 'DE' is selective and indexed, and expects about 120 rows. For each of them it probes o (orders) through idx_customer_placed, expecting about 30 rows each. The total work is roughly 120 + 120 * 30 row reads. If o showed type: ALL, that would be 120 full scans of the orders table. The join order is chosen by the optimizer from cost estimates. STRAIGHT_JOIN and optimizer hints can override it when it is wrong (see SELECT: MySQL Specifics).

FORMAT=TREE and FORMAT=JSON

The tabular output is compact but flattens the plan. Two other formats describe it more fully.

EXPLAIN FORMAT=TREE (MySQL 8.0.16 and later) prints the iterator tree that the executor will run, with cost and row estimates for every node. Read it from the innermost indented line outward:

TEXT
-> Limit: 20 row(s)  (cost=3.55 rows=20)
    -> Index lookup on orders using idx_customer_placed (customer_id=7) (reverse)  (cost=3.55 rows=31)

It names each operation in words (index lookup, filter, nested loop inner join, hash join, sort, aggregate), so a hash join or a hidden sort is easy to spot.

EXPLAIN FORMAT=JSON gives the same information as structured data, with more detail such as used_key_parts, rows_examined_per_scan, rows_produced_per_join and query_cost:

TEXT
{
  "query_block": {
    "select_id": 1,
    "cost_info": { "query_cost": "3.55" },
    "table": {
      "table_name": "orders",
      "access_type": "ref",
      "key": "idx_customer_placed",
      "used_key_parts": ["customer_id"],
      "rows_examined_per_scan": 31,
      "filtered": "100.00",
      "backward_index_scan": true
    }
  }
}

Use JSON when you want to process plans with a script or need the exact index parts used. Use the tree format to understand what actually runs.

EXPLAIN ANALYZE: estimates versus reality

EXPLAIN shows what the optimizer expects. EXPLAIN ANALYZE (MySQL 8.0.18 and later) executes the query and reports what really happened next to each estimate:

SQL
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 7 ORDER BY placed_at DESC LIMIT 20;
TEXT
-> Limit: 20 row(s)  (cost=3.55 rows=20) (actual time=0.041..0.058 rows=20 loops=1)
    -> Index lookup on orders using idx_customer_placed (customer_id=7) (reverse)  (cost=3.55 rows=31) (actual time=0.040..0.055 rows=20 loops=1)

For each node, actual time=A..B gives the milliseconds until the first row (A) and until all rows (B), per loop. rows is the real row count per loop and loops is how many times the node ran. Total time for a node is roughly B * loops. Look for:

  • Estimate versus actual rows. If the optimizer expected rows=30 and the node produced rows=250000, its statistics are wrong and it may have chosen a bad join order or index. Try ANALYZE TABLE, a histogram, or a rewrite.
  • Large loops with expensive children. A node run a million times is where the time goes.
  • Which node dominates the time, as opposed to which one looks scary.

Because EXPLAIN ANALYZE really runs the statement, use it on SELECT queries. It is mainly meant for those, so test a heavy write on a copy of the data instead.

Plain EXPLAIN is safe, because it only plans the query. EXPLAIN ANALYZE executes it, so a slow query stays slow, and it uses the real resources. Test on a replica or a copy of the data when the query is heavy.

Practicing the habit with SQLite

SQLite's EXPLAIN QUERY PLAN has far fewer columns, but it reports the same story: which tables are scanned or searched, and with which index. It is a good place to build the habit "check the plan before and after adding an index".

SQL Reading a join plan

Press Run (or Ctrl + Enter) to see the result here.

The three plans tell a story. In step 1, SQLite scans all of orders and looks each customer up by primary key. In step 2 it scans customers and searches orders through the new index. In step 3 it searches customers by country first and then orders by customer, the same driving-table choice MySQL made in the join above. The outer table is the one the optimizer expects to have the fewest rows after filtering.

SQLite has no EXPLAIN ANALYZE, but you can still compare an estimate with reality, which is its main lesson. This example gives a skewed column and checks what ANALYZE statistics predict against actual row counts:

SQL Estimates versus actual rows

Press Run (or Ctrl + Enter) to see the result here.

The estimate says about 40 rows per customer. Customer 1 really has 2,000 and customer 5 has 20. An average hides skew, and this is exactly the kind of gap EXPLAIN ANALYZE reveals in MySQL: estimated rows=40 next to actual rows=2000. Fixes include histograms on skewed columns, better composite indexes, optimizer hints or splitting the hot customer into its own code path.

A tuning loop

  1. Find a slow query (see Finding Slow Queries).
  2. Run EXPLAIN. Look for type: ALL, a missing key, big rows, low filtered, Using filesort and Using temporary.
  3. Change one thing: an index, a rewrite, a hint.
  4. Run EXPLAIN again, then EXPLAIN ANALYZE to confirm the actual time dropped.
  5. Re-test with realistic data volumes. A plan on 100 rows says little about a plan on 100 million.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In a tabular EXPLAIN, which type value means a full table scan?

    ALL reads every row of the table. index reads every entry of an index, which is cheaper but still a full scan of that index.
  2. 2. You see key_len: 8 for an index on (customer_id BIGINT NOT NULL, placed_at DATETIME). What does it tell you?

    key_len reports how many bytes of the index are used. BIGINT NOT NULL is 8 bytes, so only customer_id is used. Adding placed_at would raise it.
  3. 3. What is the difference between EXPLAIN and EXPLAIN ANALYZE?

    EXPLAIN only plans the statement. EXPLAIN ANALYZE executes it and adds actual time, rows and loops to each step, so you can compare them with the estimates.
  4. 4. Extra shows 'Using index'. What does that mean?

    Using index means a covering index. It is different from key being set, which only says that an index was used for access.
  5. 5. In EXPLAIN ANALYZE, an optimizer estimate of rows=30 but actual rows=250000 suggests what?

    A large gap between estimated and actual rows means the optimizer chose its plan from bad information. Refresh statistics, add a histogram or rewrite the query.

Summary

  • EXPLAIN shows the plan without running the query. Row order in the output is join order.
  • Read type (avoid ALL), key and key_len (which index and how many columns), rows and filtered (estimated work), then Extra (Using index is good, Using filesort and Using temporary are costs to understand).
  • FORMAT=TREE shows the execution steps in words with costs. FORMAT=JSON gives the full detail for scripts.
  • EXPLAIN ANALYZE runs the query and shows actual time, rows and loops, so you can spot where estimates fail.
  • Tune in a loop: explain, change one thing, explain again, confirm with real data volumes.

Next: Query Optimization Techniques, the rewrites that turn bad plans into good ones.