MySQL EXPLAIN Guide: What Should You Read First in an Execution Plan?
DB
Last updated on

MySQL EXPLAIN Guide: What Should You Read First in an Execution Plan?

When a MySQL query turns slow, many teams start by rewriting the SQL text. In practice, it is often faster to first inspect how MySQL plans to reach the data. That is what EXPLAIN is for.

The important mindset is that EXPLAIN is not a scorecard for whether your SQL looks elegant. It is a compact view of the access path: which table MySQL wants to read first, which index it may use, how many rows it expects to inspect, and whether extra work like sorting or temporary tables is involved.

This guide focuses on the parts that matter most in day-to-day debugging:

  • what EXPLAIN really shows
  • which columns are worth reading first
  • how to interpret type, possible_keys, key, key_len, rows, filtered, and Extra
  • where beginners tend to overreact and where they should look sooner
  • when EXPLAIN ANALYZE is worth pairing with plain EXPLAIN

The short version is this: slow-query tuning gets much easier when you stop treating SQL as text and start reading it as an access path with row volume, index choices, and extra execution work.

EXPLAIN Terminal Concept

Quick answer

In practice, a useful EXPLAIN reading order looks like this:

  1. check which table is read first and what access path was chosen
  2. inspect type for full scans or overly broad range access
  3. compare possible_keys, key, and key_len to see whether the expected index is actually being used well
  4. look at rows and filtered to estimate how much data is being read and discarded
  5. inspect Extra for Using filesort, Using temporary, and other extra work
  6. in joins, check whether row estimates explode as each step feeds the next
  7. when possible, compare the plan with real execution and use EXPLAIN ANALYZE in MySQL 8 environments

So the goal is not just “did MySQL use an index?” It is understanding how widely the query reads, how selective the path really is, and which extra steps may dominate the cost.

1. What EXPLAIN actually shows

EXPLAIN summarizes the execution plan MySQL chose for a query.

For example:

EXPLAIN
SELECT id, created_at, total_amount
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

From the result, you can usually infer:

  • which table MySQL starts with
  • which indexes it considered and which one it selected
  • how many rows it expects to inspect
  • whether it still needs extra sorting, filtering, or temporary work

That is why EXPLAIN is best thought of as a map, not a verdict. It helps you ask better questions about why the query is expensive.

2. Which columns should you read first?

You do not need to memorize every EXPLAIN column on day one. These are the most useful first:

  • type
  • possible_keys
  • key
  • key_len
  • rows
  • Extra

For joins, table and filtered are worth adding quickly too.

A practical reading flow is:

  1. which table comes first?
  2. how is that table being accessed?
  3. did MySQL choose the index you expected?
  4. how much data does it expect to read?
  5. is it doing extra work after the read?

In other words, EXPLAIN is easier to use when you read it as a sequence of questions rather than a pile of columns.

3. type is the fastest clue about access efficiency

type is one of the highest-signal columns for beginners because it tells you how MySQL is accessing rows.

At an early stage, these values are enough to build intuition:

  • const: highly selective single-row-style access
  • eq_ref: very selective join access
  • ref: index-based lookup on matching values
  • range: range-based index access
  • index: full index scan
  • ALL: full table scan

One common mistake is treating these as a simple good-to-bad ladder. That is too naive.

  • ALL is not automatically wrong if the table is tiny
  • range and ref are not automatically fast if they still touch a large portion of the table

Still, the first patterns worth questioning are usually:

  • ALL on a large table
  • range access that looks much broader than the business operation suggests
  • multiple join steps each reading a lot of rows

So type is useful not because it tells you whether the query is “good.” It tells you how broad the access path looks right away.

4. possible_keys, key, and key_len reveal index choice in context

Many beginner guides focus only on key, but the real interpretation is easier when you read three columns together.

possible_keys

This shows which indexes MySQL thinks could be candidates for the query.

If the expected index is missing here, possible causes include:

  • the needed index does not exist
  • the query shape is not index-friendly
  • functions, casts, or expressions make index usage harder

key

This is the index MySQL actually selected.

The important question is not only whether it is NULL. You also want to ask:

  • is this the index I expected?
  • if not, why did MySQL prefer another one?
  • does the chosen index help both filtering and ordering, or only part of the query?

key_len

This gives a clue about how much of the chosen index is being used.

For practical tuning, the key idea is simple:

  • a composite index may exist, but MySQL may only use part of it
  • filtering and ordering patterns affect how much of the index can actually help

So the real question is not “does the table have an index?” It is whether the chosen index matches the query shape well enough to reduce the read path and later work.

For the design side of that decision, the MySQL Index Design Guide is the natural follow-up.

5. rows and filtered help you estimate read volume

In real slow-query work, rows often provides one of the most actionable clues. It estimates how many rows MySQL expects to inspect at that step.

What matters is that rows is not the final result size. It is the amount of data MySQL may need to read along the way.

That means a query that returns 20 rows can still be expensive if it reads tens of thousands first.

filtered is useful alongside it:

  • rows: how much data MySQL expects to read
  • filtered: how much of that data it expects to keep after conditions are applied

Together, these values help you spot queries that read far too much and discard most of it.

This becomes even more important in joins. Each step can feed the next, so row estimates may compound. That is why join analysis should focus less on one scary number and more on where the row flow becomes too large.

So when rows is high, the most useful question is often not “why are there so many results?” but “why is MySQL reading so much to get the results we actually want?“

6. Extra shows signs of additional cost

Extra can look messy at first, but it often contains the clearest hints that a query is doing more work than expected.

At the beginning, these are the most useful values to recognize:

  • Using where
  • Using index
  • Using filesort
  • Using temporary

The ones that most often deserve closer attention are:

Using filesort

This means extra sorting work is needed. It is not always bad, but it can become expensive when the input set is large.

Using temporary

This means MySQL needs a temporary table, which often appears with GROUP BY, DISTINCT, or more complex sorting patterns. As data grows, this can become a noticeable cost center.

Using index

This can be a positive sign, often associated with covering-index behavior where MySQL can answer the query from the index alone. Still, it is not a magic guarantee that everything else is efficient.

So Extra is best used as a hint about what extra work happens after or around the main access path.

For the index-only read angle, the MySQL Covering Index Guide connects naturally here.

7. In joins, read the plan one step at a time

Single-table queries are usually easier to interpret. Joins are where EXPLAIN often becomes intimidating.

The trick is not to understand everything at once. Instead, read it as a sequence:

  • which table is read first?
  • how many rows does that first step produce?
  • does the next join step narrow the data or multiply the work?
  • are join keys indexed well?
  • does sorting or grouping happen after a large intermediate result has already formed?

Many slow joins are not slow because “joins are bad.” They are slow because an early step creates too much input for the later steps.

So in join-heavy plans, the most useful habit is to watch where rows suddenly grows and whether the join order seems aligned with selectivity.

For deeper join-specific tuning, continue with the MySQL Join Performance Guide.

8. Why a good-looking EXPLAIN still may not be enough

EXPLAIN is powerful, but it is still a plan and estimate.

  • data distribution may not match the optimizer’s assumptions
  • statistics may be imperfect
  • cache state and production traffic are not fully represented
  • application-level problems like repeated calls or N+1 patterns are outside the plan itself

That is why these situations need extra care:

  • the plan looks acceptable, but latency is still high
  • row estimates appear far from reality
  • the query is slow in production but not locally

In MySQL 8 environments, EXPLAIN ANALYZE is worth using when available. Plain EXPLAIN shows the chosen plan, while EXPLAIN ANALYZE gives a more reality-based view of actual rows and timing during execution.

It is still not a perfect substitute for production observation, but it is often the fastest way to spot where estimated cost and real cost diverge.

9. Common misunderstandings

1. If key is populated, the index problem is solved

Not necessarily. MySQL may be using a suboptimal index, using only part of a composite index, or still paying large sort and scan costs later.

2. If rows is small, the query must be fast

Sorting, repeated execution, join fan-out, or temporary work can still dominate.

3. ALL is always wrong

Not for tiny tables. Context matters more than the label alone.

4. EXPLAIN alone is enough to optimize queries

You still need to consider data distribution, call frequency, lock waits, and whether the application is issuing too many similar queries.

FAQ

Q. When should I use EXPLAIN first?

It is one of the best starting points when a query is slow, when you are comparing before and after an index change, or when joins and sorting begin to look suspicious.

Q. What warning signs should beginners remember first?

Start with type = ALL on large tables, key = NULL, very large rows, and Extra values like Using filesort and Using temporary.

Q. What if EXPLAIN looks fine but the query is still slow?

Check actual call frequency, data distribution, lock behavior, and whether EXPLAIN ANALYZE shows a gap between estimated and real work.

Start Here

Continue with the core guides that pull steady search traffic.

Sponsored

Related Posts

More in this category