How to Read SQL Execution Plans with EXPLAIN

When a SQL query is slow, the first instinct is often to add an index.

That may solve the problem, but it may also miss the real cause. The query might already have a usable index that the optimizer decided not to use. The join order may be inefficient, the optimizer may have estimated the wrong number of rows, or the database may be sorting a large intermediate result.

Consider the following query:

SELECT
    o.order_id,
    c.customer_name,
    o.ordered_at,
    o.total_amount
FROM orders o
JOIN customers c
  ON c.customer_id = o.customer_id
WHERE o.order_status = 'REVIEW_REQUIRED'
  AND o.ordered_at >= '2026-07-01'
ORDER BY o.ordered_at DESC
LIMIT 20;

The query returns only 20 rows, but that does not mean the database reads only 20 rows.

Internally, it might perform work like this:

Scan 10 million orders
→ Filter orders by status and date
→ Join matching orders with customers
→ Sort the result
→ Return the first 20 rows

With a suitable index, however, the same query might follow a much narrower path:

Locate recent REVIEW_REQUIRED orders in an index
→ Read only the required order rows
→ Find each customer by primary key
→ Return the first 20 rows

Both execution paths produce the same result, but the amount of work is dramatically different.

EXPLAIN helps us see which path the database intends to use.

EXPLAIN shows the table access order, access methods, selected indexes, estimated row counts, and additional operations chosen by the query optimizer.

The goal is not merely to check whether an index appears in the plan.

A useful execution-plan analysis should answer these questions:

Which table is read first?

How does the database locate rows in each table?

How many rows does it expect to examine?

How many times will later operations be repeated?

Will it need an additional sort or temporary intermediate result?

---

EXPLAIN shows the physical strategy behind a SQL query

SQL describes the result we want.

SELECT *
FROM orders
WHERE customer_id = 1001;

The statement does not explicitly say whether the database should:

• scan the entire table
• use an index on customer_id
• use a composite index
• read table rows after finding index entries
• apply one condition before another

Those decisions are made by the query optimizer.

The optimizer examines table statistics, available indexes, predicate selectivity, and estimated costs. It may compare several possible strategies:

Candidate plan A
Scan the entire orders table

Candidate plan B
Use an index on customer_id

Candidate plan C
Use a composite index and apply an additional filter

It then selects the plan that appears to have the lowest cost.

Placing EXPLAIN before the query reveals that decision:

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 1001;

A traditional MariaDB or MySQL-style EXPLAIN result commonly contains columns such as these:

Column	Meaning
id	Identifier for a query block
select_type	Type of SELECT operation
table	Table accessed at the current step
type	Access method used for the table
possible_keys	Indexes that could potentially be used
key	Index actually selected
key_len	Length of the index key used
ref	Value or column compared with the index
rows	Estimated number of rows to examine
filtered	Estimated percentage of rows that pass additional filters
Extra	Additional operations such as filtering, sorting, or covering-index access

Trying to interpret every column at once can make an execution plan seem more difficult than it is.

A practical reading order is:

1. Table access order
2. Access method in type
3. Candidate and selected indexes
4. Estimated rows
5. Estimated filtering
6. Extra work such as sorting or temporary results

---

Start by identifying the table access order

Suppose the execution plan looks like this:

id	table	type	key	rows	Extra
1	o	range	idx_orders_status_date	1,200	Using index condition
1	c	eq_ref	PRIMARY	1

For a simple join plan, this can usually be read from top to bottom:

1. Read orders first.
2. For each matching order, find the related customer.

The optimizer expects to find approximately 1,200 order rows.

For each of those rows, it uses the customer primary key to locate one customer.

Conceptually:

Find approximately 1,200 qualifying orders

For each order:
→ Read customer_id
→ Probe the customers primary-key index
→ Retrieve one customer row

The customer lookup does not happen only once.

If the first step produces 1,200 rows, the primary-key lookup may be repeated approximately 1,200 times.

1,200 estimated order rows
×
1 customer lookup per order
≈
1,200 customer index lookups

This is not an exact cost formula, but it is a useful way to understand how work multiplies across join stages.

For more complex plans containing subqueries, derived tables, CTEs, or UNION operations, the visual row order may not be enough. In those cases, id, select_type, and structured formats such as JSON become more important.

---

The type column describes how broadly the database searches

The type column is one of the first fields to inspect.

Despite its name, it does not describe a data type. It describes the method used to access rows in a table.

Common values include:

const
eq_ref
ref
range
index
ALL

These values are often presented from narrower access to broader access.

However, type alone does not determine whether a query is good or bad. A plan must also be evaluated using table size, estimated rows, actual rows, and execution frequency.

const

const commonly appears when a primary key or unique index is compared with a constant and the query can return at most one row.

SELECT *
FROM orders
WHERE order_id = 5001;

If order_id is the primary key, the plan may look like this:

type = const
key = PRIMARY
rows = 1

The optimizer can treat the matched row almost like a constant during the rest of the query.

eq_ref

eq_ref commonly appears in a join when each row from an earlier table can match at most one row in the current table.

SELECT
    o.order_id,
    c.customer_name
FROM orders o
JOIN customers c
  ON c.customer_id = o.customer_id;

If customers.customer_id is the primary key, each order can match at most one customer.

Current order row
→ Read customer_id
→ Probe the customer primary key
→ Return at most one row

eq_ref is generally an efficient join access method.

That does not mean the complete query is automatically fast.

If the earlier stage produces five million orders, the supposedly efficient primary-key lookup may still be repeated five million times.

eq_ref
≠
The complete query must be fast

ref

ref is commonly used when a non-unique index is searched for rows sharing the same value.

SELECT *
FROM orders
WHERE customer_id = 1001;

A customer may have many orders, so an index on customer_id can return multiple rows.

type = ref
key = idx_orders_customer

This access method also appears naturally in one-to-many joins.

range

range means that the database reads a specific range of an index.

SELECT *
FROM orders
WHERE ordered_at >= '2026-07-01'
  AND ordered_at <  '2026-08-01';

The database may locate the first matching key and continue through the index until the upper boundary is reached.

Locate the 2026-07-01 key
→ Read July entries sequentially
→ Stop before 2026-08-01

range can appear with inequalities, BETWEEN, and some forms of IN.

index

The name index can be misleading.

It does not necessarily mean that the database performed a narrow index lookup. It commonly means that it scanned the entire index.

Full table scan
versus
Full index scan

Scanning an index may still be cheaper than scanning the complete table, especially when the index is smaller or contains every column needed by the query.

However, the plan is still reading the index broadly.

The following interpretation is therefore incorrect:

An index name appears
→ The query must be performing a selective index search

ALL

ALL means a full table scan.

Read most or all rows in the table

When ALL appears on a large table for a highly selective query, it may indicate:

• a missing index
• an unusable index
• an expression preventing index access
• a type conversion
• an inaccurate row estimate
• an index the optimizer considers more expensive than scanning

A full table scan is not always wrong.

It may be reasonable when:

• the table is small
• most rows are required
• the predicate matches a large percentage of the table
• indexed access would require too many random table lookups
• the optimizer correctly estimates that scanning is cheaper

Suppose 90% of orders have the COMPLETED status:

SELECT *
FROM orders
WHERE order_status = 'COMPLETED';

Even if an index exists on order_status, using it to retrieve most of the table may be more expensive than a sequential scan.

An index does not have to be used simply because it exists.

---

Read possible_keys and key together

possible_keys lists indexes that the optimizer considers potentially usable for the current access condition.

key shows the index selected for the plan.

For example:

possible_keys	key
idx_orders_status, idx_orders_status_date	idx_orders_status_date

The optimizer considered both indexes and selected the composite index.

Another plan may look like this:

possible_keys	key
idx_orders_status	NULL

The index is technically relevant, but the optimizer chose not to use it.

Index appears in possible_keys
→ It could support part of the condition

key is NULL
→ It is not used in the selected plan

This does not immediately mean that the optimizer made a mistake.

Check the following:

• How many rows match the condition?
• Does the query request most table columns with SELECT *?
• Would indexed access require many table-row lookups?
• Is the table small?
• Are the statistics current?
• Is the indexed value distribution highly skewed?

If possible_keys is also NULL, there may be no directly useful index for the current condition.

That is not necessarily a problem if scanning the table is inherently appropriate.

---

key_len helps reveal how much of an index is being used

Suppose the following composite index exists:

CREATE INDEX idx_orders_status_date_customer
ON orders (
    order_status,
    ordered_at,
    customer_id
);

key_len reports the length of the index key used by the access path.

It can help determine whether the plan is using only the leading column or a larger portion of the composite index.

However, it is not a simple count of index columns.

The reported byte length depends on factors such as:

• data type
• string length
• character set
• whether NULL is allowed
• index prefix length

A value such as key_len = 8 should therefore be interpreted together with the table and index definitions, not memorized in isolation.

---

ref shows what the index is compared against

The ref column shows the value used to search the selected index.

It may display:

const

This means that the index is compared with a constant value, such as:

WHERE order_status = 'REVIEW_REQUIRED'

In a join, ref may contain a column from an earlier table:

app.o.customer_id

This indicates that the current table’s index is probed using orders.customer_id.

---

Use rows and filtered to estimate how much data moves through the plan

rows is the optimizer’s estimate of how many rows it will examine at the current step.

The key word is estimate.

rows = 1,200

does not guarantee that exactly 1,200 rows will be read at runtime.

The estimate can be inaccurate when statistics are outdated or data distribution is uneven.

filtered is the estimated percentage of those rows that will survive additional conditions.

Suppose the plan reports:

rows = 100,000
filtered = 10.00

The optimizer expects to read approximately 100,000 rows and pass about 10% of them to the next stage.

100,000 × 10%
≈ 10,000 rows

This is still only a rough estimate.

In a join, the number of rows passed from one stage affects how many times later operations may run.

Consider this plan:

table	type	rows	filtered
o	ALL	10,000,000	0.01
c	eq_ref	1	100.00

Only about 1,000 order rows may survive the filter, but the database expects to examine approximately 10 million orders to find them.

Rows passed forward
≈ 1,000

Rows examined in orders
≈ 10,000,000

A query is not efficient merely because its final result is small.

---

Incorrect estimates can produce poor plans

Suppose the optimizer expects 1,000 REVIEW_REQUIRED orders, but the real number is two million.

Estimated
1,000 orders
→ 1,000 customer lookups

Actual
2,000,000 orders
→ 2,000,000 customer lookups

A nested-loop strategy may look inexpensive when based on the estimate but become extremely expensive at runtime.

Large estimation errors can result from:

• stale statistics
• strongly skewed data
• correlated predicates
• functions applied to indexed columns
• implicit data-type conversions
• parameter values with very different selectivity

When reading an execution plan, a small estimate is less meaningful if it does not match reality.

---

Extra reveals additional work

The Extra column describes operations not fully represented by the basic access method.

These messages should not automatically be treated as errors. Each one describes something the database expects to do.

Using where

Using where means that additional predicates are evaluated after rows are read from the selected access path.

Read candidate row
→ Evaluate WHERE condition
→ Keep the row if it passes

This can appear even when an index is used.

An index may narrow the search using some conditions, while other predicates still have to be checked afterward.

The important question is not whether Using where appears.

It is:

How many rows are read before that filtering occurs?

Using index

Using index often means that the query can be satisfied using only data stored in the index.

This is commonly associated with a covering index.

Typical indexed access
Search index
→ Locate table row
→ Read table data
→ Return result

Covering-index access
Search index
→ Read required values from the index
→ Return result

Avoiding table-row lookups can improve performance.

However, adding every requested column to an index merely to produce Using index can make the index large and expensive to maintain.

Using index condition

Using index condition is associated with Index Condition Pushdown.

The storage engine can evaluate an additional predicate while examining index entries, reducing unnecessary table-row lookups.

Read index entry
→ Evaluate additional index condition
→ Access the table row only if needed

The index may not fully determine the range, but it can still reduce the number of table rows read.

Using filesort

Using filesort means that the requested output order cannot be obtained directly from the selected index order, so a separate sorting operation is needed.

ORDER BY o.ordered_at DESC

The execution may look like this:

Read matching rows
→ Build sort input
→ Sort by ordered_at
→ Return the first 20 rows

Despite the name, filesort does not mean that the sort must always be written to a disk file.

Depending on the amount of data and available memory, the operation may happen in memory or use temporary storage.

Using filesort also does not always mean that another index should be added.

Sorting a few dozen rows may be inexpensive. Sorting several million rows before applying LIMIT 20 may be a serious bottleneck.

Using temporary

Using temporary means that the database may use an intermediate structure to process operations such as:

• GROUP BY
• DISTINCT
• sorting
• certain complex joins or derived results

Conceptually:

Read source rows
→ Build intermediate result
→ Group, deduplicate, or reorganize rows
→ Produce final result

This is not automatically a bad plan.

The important questions are how large the temporary result becomes, how often the query runs, and whether temporary storage spills beyond memory.

---

Reading one execution plan from beginning to end

Return to the original query:

SELECT
    o.order_id,
    c.customer_name,
    o.ordered_at,
    o.total_amount
FROM orders o
JOIN customers c
  ON c.customer_id = o.customer_id
WHERE o.order_status = 'REVIEW_REQUIRED'
  AND o.ordered_at >= '2026-07-01'
ORDER BY o.ordered_at DESC
LIMIT 20;

Assume the plan looks like this:

table	type	possible_keys	key	rows	filtered	Extra
o	ALL	idx_orders_status	NULL	10,000,000	0.01	Using where; Using filesort
c	eq_ref	PRIMARY	PRIMARY	1	100.00

We can now read the plan step by step.

1. orders is the starting table

The first step accesses orders.

2. orders is fully scanned

type = ALL
rows = 10,000,000

The optimizer expects to examine approximately 10 million order rows.

3. A candidate index exists but is not selected

possible_keys = idx_orders_status
key = NULL

The status index is technically relevant, but the optimizer decided not to use it.

Possible reasons include:

• too many rows have the target status
• the status-only index does not support the date condition well
• indexed access would require too many table lookups
• the index does not support the requested order
• the statistics do not reflect the real distribution

4. Most examined rows are discarded

filtered = 0.01

The optimizer expects only about 0.01% of the 10 million rows to survive.

Read 10,000,000 rows
→ Keep approximately 1,000

The difference between rows examined and rows passed forward is substantial.

5. A separate sort is required

Using filesort

The qualifying orders must be sorted by ordered_at DESC before the first 20 are returned.

6. Each qualifying order looks up one customer

customers
type = eq_ref
key = PRIMARY
rows = 1

The customer lookup itself is efficient.

The main problem is not the join to customers.

The main problem is finding and ordering the relevant rows from orders.

Not the primary bottleneck
Customer primary-key lookup

Likely bottleneck
Full scan of 10 million orders
+
Separate sorting

Without this analysis, adding another index to the customer table would optimize the wrong part of the query.

---

Compare the plan after changing the index

The query filters and sorts using these columns:

order_status = equality condition
ordered_at = range condition
ORDER BY ordered_at DESC

A composite index worth evaluating is:

CREATE INDEX idx_orders_status_date
ON orders (
    order_status,
    ordered_at
);

The index groups entries by status and orders entries within each status by ordered_at.

REVIEW_REQUIRED
→ ordered_at order

PAID
→ ordered_at order

Assume the new plan looks like this:

table	type	key	rows	filtered	Extra
o	range	idx_orders_status_date	1,000	100.00	Using index condition
c	eq_ref	PRIMARY	1	100.00

The new plan can be read as follows:

orders
→ Use the composite index
→ Read the required status and date range
→ Expect approximately 1,000 rows

customers
→ Use the primary key for each matching order
→ Return one customer row

Before:

Scan 10 million orders
→ Filter rows
→ Sort the surviving result

After:

Locate the relevant index range
→ Process only qualifying order candidates

The new plan appears more efficient, but the optimization process is not finished until it is measured.

Check whether:

• actual response time improved
• the number of examined rows decreased
• the separate sort was removed or reduced
• the new index increased write cost significantly
• the index helps other important queries
• it overlaps unnecessarily with existing indexes

An index is both a read optimization structure and an additional cost for storage, inserts, updates, and deletes.

---

EXPLAIN is usually an estimated plan

A traditional EXPLAIN generally shows estimates produced by the optimizer:

Estimated row count
Estimated filtering ratio
Estimated access cost

These estimates may differ from actual execution.

For example:

ItemEstimatedActual

REVIEW_REQUIRED orders	1,000	2,000,000
Customer lookups	1,000	2,000,000
Rows to sort	1,000	2,000,000

When the estimates are this inaccurate, the shape of the estimated plan may not reveal the true runtime problem.

Depending on the database product and version, actual execution statistics may be available through commands such as:

EXPLAIN ANALYZE
SELECT ...

MariaDB environments may provide related analysis commands and JSON-based formats, for example:

ANALYZE FORMAT=JSON
SELECT ...

The exact syntax and output vary by database and version, so the capabilities of the current environment should be checked.

Runtime analysis can reveal:

• actual rows processed
• number of loops at each stage
• estimated versus actual row counts
• stage-level execution times
• actual filtering ratios

These commands may execute the query rather than only describe it.

Use them carefully in production, especially for:

• large scans
• long-running queries
• operations that hold locks
• INSERT, UPDATE, or DELETE
• statements connected to external side effects

---

JSON format helps with complex plans

The traditional table format is convenient for a quick inspection of simple queries.

It can become difficult to follow when the query contains:

• several joins
• nested subqueries
• CTEs
• derived tables
• materialization
• UNION operations

A structured format can provide more detail:

EXPLAIN FORMAT=JSON
SELECT
    o.order_id,
    c.customer_name
FROM orders o
JOIN customers c
  ON c.customer_id = o.customer_id
WHERE o.order_status = 'REVIEW_REQUIRED';

Depending on the database and version, JSON output may expose information such as:

• query blocks
• nested-loop structure
• selected access paths
• predicates applied at each stage
• estimated row counts
• cost information
• materialized subqueries
• derived-table processing
• predicate placement

It is usually easier to begin with the traditional table output, locate the suspicious part, and then use JSON when the execution structure requires more detail.

---

A practical order for reading execution plans

When analyzing a slow query, the following sequence works well.

1. Verify that the SQL returns the correct result

Check for missing join predicates, incorrect filters, unintended duplicates, and wrong aggregation.

Making an incorrect query faster is not optimization.

2. Understand the role and size of each table

Ask:

Which table is large?

Which condition should reduce the data most?

What does one result row represent?

3. Identify the access order

Check whether the plan reduces the data early or sends a large intermediate result into later joins.

4. Inspect type

When ALL or index appears on a large table, determine whether reading such a broad range is reasonable.

5. Compare possible_keys and key

Distinguish between:

• no usable index
• an index that exists but was not selected
• an index that was selected but still reads many rows

6. Inspect rows and filtered

Estimate how many rows are examined and how many move to the next stage.

7. Think in terms of repeated join work

Connect the first table’s output size with the number of lookups performed against later tables.

8. Inspect Extra

Look for:

• large additional filtering
• separate sorting
• temporary results
• covering-index access
• index-condition pushdown

9. Compare estimates with actual execution

Use runtime analysis, slow-query logs, and monitoring data where appropriate.

10. Measure again after every change

Compare index and query changes using the same data volume, parameters, and workload conditions.

---

Common mistakes when reading EXPLAIN

An index appears in key, so the query must be fast

A query can use an index and still read millions of entries.

Index usage
≠
Low processing cost

Always inspect the access type, estimated rows, actual rows, and lookup repetition.

ALL always means that an index should be added

A full scan may be correct for a small table or a query that needs most rows.

rows is the actual number of rows read

In a traditional plan, rows is generally an estimate based on statistics.

Using where means the plan is bad

It only means that an additional condition is evaluated after candidate rows are read.

The number of rows reaching that condition is more important.

Using filesort always means disk sorting

It means that a separate sort is required. It does not guarantee that the sort is written to disk.

Fixing the worst type value is enough

The main cost may come from:

• a large earlier result
• millions of repeated lookups
• an expensive sort
• a large temporary result
• inaccurate estimates

The complete execution flow matters more than one column value.

---

Key concepts for the Information Processing Engineer exam

ItemKey	meaning
EXPLAIN	Displays the estimated execution plan
table	Table accessed at the current step
type	Table access method
possible_keys	Candidate indexes
key	Index selected by the optimizer
key_len	Length of the index key used
ref	Value compared against the index
rows	Estimated number of rows examined
filtered	Estimated percentage passing additional filters
Using index	Query may be satisfied using only index data
Using where	Additional condition applied to read rows
Using filesort	Separate sorting operation required
Using temporary	Temporary intermediate structure used

Common access methods can be summarized as follows:

typeKey	meaning
const	At most one row found through a primary or unique key
eq_ref	At most one row found for each earlier join row
ref	Multiple matching rows found through a non-unique index
range	A specific range of an index is scanned
index	The entire index is scanned
ALL	The entire table is scanned

Certification questions may focus on these definitions.

In real performance work, however, the full data flow matters more than any single value.

---

An execution plan is the database’s work proposal

Reading EXPLAIN properly means more than checking whether the key column contains an index name.

You should be able to describe the plan in plain language:

Which table is read first?

How many rows are expected from the first step?

Which condition and index are used?

How many times is the next table searched?

Is a separate sort or temporary result required?

Do the optimizer's estimates match the real data?

An execution plan is not an unquestionable answer.

It is closer to a proposed work plan created from the optimizer’s current statistics and cost model.

That is why query analysis should combine three perspectives:

The logical result expressed by the SQL

The estimated strategy shown by EXPLAIN

The actual work measured at runtime

The key idea can be summarized in one sentence:

EXPLAIN shows where the database expects to begin reading, which indexes it plans to use, how many rows it expects to process, and what additional work it expects to perform.

A good execution plan does more than use an index.

It:

• locates the required range efficiently
• avoids examining unnecessary rows
• prevents large intermediate results from flowing into later stages
• limits repeated join lookups
• avoids unnecessary sorting and temporary work
• estimates the real data distribution with reasonable accuracy

SQL tuning is not the process of forcing a particular value to appear in the type column.

It is the process of reducing the total amount of data the database must read, compare, join, sort, and temporarily store to produce the result.

References

• Chapter 14: Indexing — Abraham Silberschatz, Henry F. Korth, and S. Sudarshan / Database System Concepts
• Essentials of an Index Guide — MariaDB
• Getting Started with Indexes Guide — MariaDB
• Storage Engine Index Types — MariaDB
• Primary Key Constraints — MariaDB
• Compound (Composite) Indexes — MariaDB
• Building the Best Index for a Given SELECT — MariaDB
• Statistics for Optimizing Queries — MariaDB
• Histogram-Based Statistics — MariaDB
• ANALYZE TABLE — MariaDB
• EXPLAIN — MariaDB
• ANALYZE Statement — MariaDB
• ANALYZE FORMAT=JSON — MariaDB
• Index Condition Pushdown — MariaDB
• Index Hints: How to Force Query Plans — MariaDB
• Pagination Optimization — MariaDB