Understanding MySQL EXPLAIN: Usage, Output Fields, and Practical Examples

The EXPLAIN statement in MySQL is used to display the execution plan of a SELECT query, helping developers identify performance bottlenecks and optimize their SQL.

Basic Usage

Typical usage is: mysql> explain select * from students; For more detailed information, EXPLAIN EXTENDED adds a filtered column.

mysql> explain extended select * from students;

Output Columns Explained

id : Identifier of the SELECT step.

select_type : Type of SELECT (SIMPLE, PRIMARY, UNION, SUBQUERY, etc.).

table : Table referenced in the output row.

type : Join type, ordered from best to worst (system, const, eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL).

possible_keys : Indexes MySQL could use.

key : Index actually used (NULL if none).

key_len : Number of bytes of the index used.

ref : Columns or constants used to look up the index.

rows : Estimated number of rows MySQL thinks it must examine.

filtered : Percentage of rows filtered by table conditions (only with EXTENDED).

Extra : Additional information such as Using where, Using index, Using filesort, etc.

Notes and Limitations

EXPLAIN does not show the impact of triggers, stored procedures, or user-defined functions.

It ignores cache effects.

It cannot display the actual optimizations performed by MySQL.

Statistics are estimates, not exact values.

EXPLAIN only works for SELECT statements; other statements must be rewritten as SELECT to view their plans.

Practical Examples

Show indexes on the students table: mysql> show keys from students; Explain a primary‑key lookup:

mysql> explain select * from students where stud_id = '1';

Result:

+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| 1  | SIMPLE      | students | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+

Explain an indexed range query:

mysql> explain select * from students where create_date = '2010-01-01';

Result includes type = ref and Using where in the Extra column.

Summary

EXPLAIN cannot reveal trigger, stored procedure, or UDF effects.

Cache behavior is not considered.

It does not show the actual optimizations MySQL performs.

Statistics are approximations.

Only SELECT statements can be explained directly.

Reference

For a deeper dive, see the original article "EXPLAIN 命令详解" and related resources linked in the source.