Understanding MySQL EXPLAIN Output: Columns and Their Meaning

Everyone is familiar with EXPLAIN as a way to see how MySQL executes a query; while not 100% reliable, it provides a clear view of the execution plan for most scenarios.

id

The id column shows the query identifier. For simple queries without subqueries or UNIONs, there is only one row. With UNIONs, an extra row with NULL appears to mark the combined result, and the temporary table name appears as .

select_type

This column indicates the type of SELECT.

SIMPLE : a simple query without subqueries or UNION .

PRIMARY : the outermost SELECT of a complex query.

UNION & UNION RESULT : the first SELECT in a UNION is marked PRIMARY , the following ones UNION , and the final combined result UNION RESULT .

DERIVED : a subquery appearing in the FROM clause, materialized as a temporary table.

SUBQUERY : a subquery not appearing in FROM.

DEPENDENT : a correlated subquery that uses columns from the outer query; it can combine with UNION or SUBQUERY to produce different results.

UNCACHEABLE : a subquery that cannot be cached, similar in behavior to DEPENDENT .

MATERIALIZED : MySQL's optimization that stores the result of a subquery in a temporary table so it runs only once. The field FORMAT=JSON materialized_from_subquery can be used to inspect this.

table

Shows the table name used in the step; UNION_RESULT and DERIVED tables have generated names such as or .

partitions

Displays partition information if the table is partitioned; otherwise it can be ignored.

type

Describes the join type and access method, ordered from fastest to slowest:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL.

system & const : fastest, often a primary key or unique index lookup; system is a special case of const for single‑row system tables.

eq_ref : uses a primary or unique index and returns at most one row. Example column: user_id .

ref : index lookup that may return multiple rows (e.g., left‑most prefix or non‑unique index).

fulltext : uses a FULLTEXT index.

ref_or_null : similar to ref but also searches for NULL values, useful for IS NULL conditions.

index_merge : combines results from multiple indexes (introduced in MySQL 5.1) for queries like WHERE name = ? OR age = ? .

unique_subquery : a special case of eq_ref for queries using IN with a unique index.

index_subquery : similar to unique_subquery but for non‑unique indexes.

range : index range scan, used for conditions like BETWEEN ... AND ... , > , < , LIKE , IN , etc.

index : scans the table in index order, similar to a full table scan but using the index.

ALL : full table scan.

possible_keys

Lists the indexes that MySQL could consider using for the query.

key

Shows the actual index chosen by the optimizer.

key_len

Indicates the maximum length of the index parts used; it may be longer than the actual data length.

ref

Displays the column or constant that the chosen index uses.

rows

Estimates the number of rows MySQL expects to examine; useful for judging query efficiency.

filtered

Added in MySQL 5.1, this is the estimated percentage of rows that satisfy the filter condition; multiplying rows by filtered gives an estimate of rows after filtering.

Extra

Provides additional execution details. Common values include:

Using index : covering index is used.

Using index condition : index condition pushdown is applied.

Using temporary : a temporary table is used for sorting or grouping.

Using filesort : MySQL performs an external sort.

Using where : a WHERE filter is applied.

Zero limit : the query contains LIMIT 0 .

Using sort_union(), Using union(), Using intersect() : index merge operations are in effect.

Summary

The EXPLAIN output provides a wealth of information about how MySQL will execute a query; understanding each column helps you identify bottlenecks, choose appropriate indexes, and rewrite queries for better performance.