Master MySQL EXPLAIN: Decode Every Column and Boost Query Performance

Overview

This article explains how to interpret MySQL EXPLAIN output, covering every column, join type, and the information shown in the Extra column. It also demonstrates the use of EXPLAIN EXTENDED, SHOW PROFILE, and the optimizer trace for in‑depth query analysis. Repository with examples: https://github.com/Wasabi1234/Java-Interview-Tutorial

Columns in EXPLAIN Output

id

The id column indicates the execution order of SELECT statements. Rows with the same id are processed top‑to‑bottom; a larger id (e.g., in a subquery) has higher priority and is executed earlier.

EXPLAIN SELECT * FROM emp e JOIN dept d ON e.deptno = d.deptno JOIN salgrade sg ON e.sal BETWEEN sg.low_sal AND sg.high_sal;

select_type

Identifies the type of SELECT:

simple – no joins or subqueries

primary – outer query

union – second or later SELECT in a UNION

subquery – first SELECT in a subquery

table

Shows the table (or derived/union result) from which the row is read.

type

Describes how MySQL accesses rows. From worst to best: ALL, index, range, ref, eq_ref, const, system, NULL.

ALL

Full table scan – the least efficient method.

EXPLAIN SELECT * FROM film WHERE rating > 9;

index

Scans the index tree. If the index covers all needed columns, Extra shows Using index.

range

Uses an index to retrieve rows within a specific range (e.g., =, BETWEEN, IN).

SELECT * FROM tbl_name WHERE key_column = 10;

index_subquery

Replaces an IN subquery when a non‑unique index can be used.

value IN (SELECT key_column FROM single_table WHERE some_expr);

unique_subquery

Similar to index_subquery but works with a unique index.

value IN (SELECT primary_key FROM single_table WHERE some_expr);

index_merge

Combines multiple indexes to satisfy the query.

ref_or_null

Like ref but also matches rows with NULL values.

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

fulltext

Uses a FULLTEXT index.

ref

Uses a non‑unique index for rows that match the indexed value.

SELECT * FROM ref_table WHERE key_column=expr;

eq_ref

Best join type (after system and const) – reads at most one row per combination using a PRIMARY or UNIQUE NOT NULL index.

SELECT * FROM ref_table, other_table WHERE ref_table.key_column = other_table.column;

const

Table has at most one matching row, read once at query start.

SELECT * FROM tbl_name WHERE primary_key = 1;

system

Special case of const for system tables.

type NULL – MySQL returns the result without accessing any table.

possible_keys

Indexes that MySQL could consider for the query.

key

The index actually chosen.

ref

Column or constant used together with the chosen key.

rows

Estimated number of rows MySQL expects to examine.

filtered

Estimated percentage of rows filtered by the query condition.

extra

Additional execution‑plan information. Important values include:

Using filesort – MySQL must sort rows after retrieval because ORDER BY cannot use an index.

Using temporary – MySQL creates a temporary table to hold intermediate results.

Using index – The query is covered by an index; data is read directly from the index.

Using where – Rows are filtered using a WHERE clause.

Using join buffer – MySQL uses a join buffer for the join.

Impossible where – The WHERE condition is always false.

No matching row in const table – A constant (derived) table has no rows that satisfy the join condition.

EXPLAIN EXTENDED

Introduced in MySQL 4.1. Running EXPLAIN EXTENDED followed by SHOW WARNINGS reveals the exact query the optimizer will execute after simplifying constant expressions.

EXPLAIN SELECT * FROM users; SHOW WARNINGS;

SHOW PROFILE

SHOW PROFILE

and SHOW PROFILES display resource usage for statements executed in the current session (deprecated in recent versions). To enable profiling for a session: SET profiling = 1; After running a query, you can inspect detailed timing information, e.g., CPU usage: SHOW PROFILE CPU FOR QUERY 6; The Sending data state often dominates execution time because the server reads rows from disk.

Optimizer Trace (MySQL 5.6+)

The optimizer trace provides a JSON‑formatted log of the optimizer’s decision process.

SET optimizer_trace = "enabled=on", end_markers_in_json = on;

SET optimizer_trace_max_mem_size = 1000000;

Execute the target query, then query information_schema.optimizer_trace to view the trace.

SELECT * FROM information_schema.optimizer_trace;

References

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-join-types