Master MySQL EXPLAIN: Decode Execution Plans Like a Pro

Introduction

Performance optimization is essential for developers and DBAs; this guide introduces a tool to examine whether a SQL statement uses the correct index by interpreting the output of the EXPLAIN command.

Test Environment

MySQL 5.7 is used for the examples.

create table a (
  id   bigint(20) NOT NULL AUTO_INCREMENT,
  name varchar(50) NOT NULL DEFAULT '',
  age  INT(11) DEFAULT 0,
  primary key (id),
  key idx_name (name)
) engine=innodb default charset=utf8;

insert into a (name, age) values ('yy',11);
insert into a (name, age) values ('xx',25);
insert into a (name, age) values ('yz',23);
insert into a (name, age) values ('lisi',18);
insert into a (name, age) values ('boshi',62);
insert into a (name, age) values ('malong',32);
insert into a (name, age) values ('taisen',52);
insert into a (name, age) values ('liuxiang',32);
insert into a (name, age) values ('jingtian',28);

create table b (
  id   bigint(20) NOT NULL AUTO_INCREMENT,
  sid  int NOT NULL DEFAULT 0,
  name varchar(50) NOT NULL DEFAULT '',
  score INT(11) DEFAULT 0,
  primary key (id),
  key idx_sid (sid),
  key idx_name (name)
) engine=innodb default charset=utf8;

insert into b (sid, name, score) values (1,'yy',99);
insert into b (sid, name, score) values (2,'xx',95);
insert into b (sid, name, score) values (3,'yz',85);
insert into b (sid, name, score) values (4,'jingtian',78);
insert into b (sid, name, score) values (5,'jingtian',90);
insert into b (sid, name, score) values (6,'boshi',83);
insert into b (sid, name, score) values (7,'taisen',85);
insert into b (sid, name, score) values (8,'liuxiang',81);
insert into b (sid, name, score) values (9,'malong',92);
insert into b (sid, name, score) values (10,'jingtian',78);

Result Introduction

Running EXPLAIN SELECT * FROM a WHERE id=3 \G produces output such as:

id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL

The columns displayed include id, type, table, select_type, possible_keys, key, key_len, ref, rows, and Extra. The article focuses on the SELECT statement as an example to explain the EXPLAIN output.

Interpretation

id

The id column identifies each SELECT operation in the execution plan. A lower id means the operation is executed later. When ids are the same, the order of table access follows the order they appear in the query.

Two rows with id=1: the optimizer accesses table b first, then table a .

When ids differ, the larger id is processed first.

In this case, the subquery with id=2 runs before accessing table a .

select_type

The select_type column describes the type of SELECT operation. Common values include:

SIMPLE – a straightforward SELECT without subqueries or UNION.

PRIMARY – the outermost SELECT when the query contains subqueries.

SUBQUERY – a SELECT that appears in the SELECT list or WHERE clause.

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

UNION – a SELECT that follows a UNION.

UNION RESULT – the combined result of UNION queries.

table

The table column shows the table name or alias being accessed. For derived tables, the name appears as <derivedN>. Temporary tables created for subqueries have no indexes.

type

The type column indicates how MySQL accesses the table, ordered from best to worst:

system

const

eq_ref

ref

range

index

ALL

Examples and diagrams illustrate each access method.

possible_keys

possible_keys lists the indexes MySQL could use for the query.

key

key shows the actual index chosen by the optimizer.

key_len

key_len reports the length of the index used (in bytes), helping to determine how many columns of a composite index are applied.

rows

The rows column is an estimate of how many rows MySQL expects to examine; lower values are better.

extra

The Extra column provides additional information, such as: Using index – the query uses a covering index, avoiding a table lookup. Using where – a table lookup is required to evaluate the WHERE clause. Using filesort – MySQL must sort rows using an extra pass.

Other positive hints include Using MRR, Using index condition, and Using index for group-by.

Conclusion

The article demonstrates how to read MySQL EXPLAIN output using concrete examples, helping developers and DBAs evaluate and optimize SQL execution plans effectively.