Master MySQL EXPLAIN: Decode Execution Plans and Optimize Queries

EXPLAIN Overview

The EXPLAIN statement can be prefixed to SELECT, DELETE, INSERT, REPLACE, and UPDATE queries to view their execution plans.

Creating Test Tables

CREATE TABLE t1 (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 VARCHAR(100),
    key3 VARCHAR(100),
    name VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    KEY idx_key2_key3 (key2, key3)
) ENGINE=InnoDB CHARSET=utf8;

CREATE TABLE t2 (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 VARCHAR(100),
    key3 VARCHAR(100),
    name VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    KEY idx_key2_key3 (key2, key3)
) ENGINE=InnoDB CHARSET=utf8;

EXPLAIN Variants

EXPLAIN EXTENDED

Provides additional optimizer information; after running SHOW WARNINGS you can see the rewritten query.

EXPLAIN EXTENDED SELECT * FROM t1 WHERE key1 = '11';
SHOW WARNINGS;

EXPLAIN PARTITIONS

Shows which partitions are accessed when the query involves a partitioned table.

EXPLAIN PARTITIONS SELECT * FROM t1 INNER JOIN t2 ON t1.key3 = t2.key3;

Key Columns in EXPLAIN Output

table column

Indicates which table a row in the plan refers to.

EXPLAIN SELECT * FROM t1;

When a join is involved, each table gets its own row in the plan.

EXPLAIN SELECT * FROM t1 INNER JOIN t2;

id column

Represents the execution order of SELECT statements. Larger id values have higher priority; equal ids are processed top‑to‑bottom; NULL means the step runs last.

EXPLAIN SELECT * FROM t1 WHERE key1 = 'e038f672a8';

In a join, rows share the same id because they belong to the same SELECT.

EXPLAIN SELECT * FROM t1 INNER JOIN t2;

select_type column

Describes the role of each SELECT in a complex query. Values include SIMPLE , PRIMARY , UNION , UNION RESULT , SUBQUERY , DEPENDENT SUBQUERY , DEPENDENT UNION , DERIVED , MATERIALIZED , etc.

Examples:

SIMPLE : a single‑table query without UNION or subqueries.

PRIMARY : the leftmost SELECT in a UNION or a top‑level SELECT.

UNION and UNION RESULT : indicate the temporary table used for deduplication.

SUBQUERY : a non‑correlated subquery that cannot be transformed into a semi‑join.

DEPENDENT SUBQUERY : a correlated subquery.

DERIVED : a subquery that is materialized as a derived table.

MATERIALIZED : the optimizer chose to materialize a subquery before joining.

type column

Shows the access method MySQL will use, ordered from best to worst: system > const > eq_ref > ref > range > index > ALL. Aim for range or better.

Examples of common types:

system / const : constant lookup, usually primary key equality.

eq_ref : unique index lookup returning at most one row.

ref : non‑unique index lookup that may return multiple rows.

index_merge : MySQL merges results from multiple indexes.

range : index range scan (e.g., BETWEEN, IN).

index : full index scan.

ALL : full table scan.

possible_keys and key columns

possible_keys

lists indexes that could be used; key shows the index actually chosen. If key is NULL, MySQL decided a full table scan is cheaper.

key_len column

Indicates how many bytes of the index MySQL will use. For fixed‑length types the length equals the type size; for variable‑length types it includes length bytes and a possible NULL byte.

rows column

Estimates the number of rows MySQL expects to read for that step.

ref column

Shows the constant or column used with the index. Values can be const, a column name, or func when a function is involved.

Extra column

Provides additional details such as:

Using index : covering index, no need to read the table.

Using where : a WHERE filter is applied after row retrieval.

Using temporary : a temporary table is created (e.g., for GROUP BY, UNION, DISTINCT).

Using filesort : MySQL must sort rows externally.

Using join buffer (Block Nested Loop) : join buffer is used for a nested‑loop join.

No tables used : query without a FROM clause.

Impossible WHERE : the WHERE condition is always false.

Practical Tips

Prefer indexes that reduce type to ref or better.

Watch the rows estimate; a high value often means a full scan.

When Using temporary or Using filesort appear, consider adding or adjusting indexes.

For subqueries, check if the optimizer rewrites them to joins; the id values can reveal this.

By interpreting each column of the EXPLAIN output, you can pinpoint bottlenecks and apply appropriate indexing or query rewrites to achieve faster query execution.