Unlock MySQL Performance: Mastering EXPLAIN Execution Plans

What is an execution plan?

Using the EXPLAIN keyword simulates the optimizer executing an SQL query, revealing how MySQL processes your statement and helping analyze performance bottlenecks in the query or table schema.

Official documentation: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

Prerequisite: all examples use MySQL version 5.7.23.

What does an execution plan help us achieve?

Table read order

Data read operation types

Which indexes can be used

Which indexes are actually used

Table references

Number of rows examined per table

How to use an execution plan?

explain + SQL statement

Execution plan contains information

Explanation of execution plan fields

id

The sequence number of the SELECT operation, a set of numbers indicating the order in which SELECT clauses or tables are processed.

use oemp;

# test table 1
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

# test table 2
CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

# test table 3
CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `other_column` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

# id same
explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t3.other_column = '';

# id different
explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.other_column = ''));

# id same and different together
explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1, t2 where s1.id = t2.id;

Three scenarios are covered: identical ids, different ids, and a mix of identical and different ids.

Same id

When ids are the same, execution proceeds from top to bottom.

Execution result

Different ids

When ids differ in a subquery, the id numbers increase; larger id values have higher priority and are executed first.

Execution result

Mixed identical and different ids

If ids are identical, they are treated as a group and executed top‑to‑bottom; across all groups, larger id values have higher priority and are executed first. Derived = DERIVED .

Execution result

MySQL 5.7 introduced derived_merge . It attempts to merge derived tables (subqueries in FROM) and common table expressions with the outer query. The behavior can be controlled via optimizer_switch: set optimizer_switch='derived_merge=off'; When the switch is on , the derived_merge step is omitted, as shown in the following image:

select_type

Indicates the type of SELECT: simple, primary, subquery, derived, union, union result. It distinguishes ordinary queries from complex ones such as unions and subqueries.

simple – a straightforward SELECT without subqueries or UNION

primary – the outermost SELECT when the query contains complex subparts

subquery – a SELECT appearing in the SELECT or WHERE list

derived – a SELECT in the FROM clause, executed recursively and stored in a temporary table

union – a SELECT that appears after UNION; if UNION is inside a FROM subquery, the outer SELECT is marked as derived

union result – rows retrieved from a UNION result set

table

This row refers to which table the data belongs to.

type

Access type, ordered from best to worst: system > const > eq_ref > ref > range > index > all . Types include all, index, range, ref, eq_ref, const, system, null.

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

Note: Ensure queries achieve at least range level, preferably ref .

Null

explain select min(id) from t1;

MySQL can evaluate the query during the optimization phase without accessing the table or index, e.g., retrieving the minimum value directly from an indexed column.

system

A table with a single row (system table) behaves like a const type; it is rarely encountered.

count

explain select * from (select * from t1 where id=1) d1;

When the optimizer can locate a row using an index in a single step, the query is treated as const . This is fast because the primary key or a unique index matches only one row.

eq_ref

explain select * from t1, t2 where t1.id = t2.id;

Unique index scan: for each index key, only one row matches. Common with primary or unique indexes.

ref

# tb_emp DDL
CREATE TABLE `tb_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

# add age column
alter table tb_emp add column `age` int(11) default null after `name`;

# add composite index
create index idx_emp_name_age on tb_emp(`name`, `age`);

explain select * from tb_emp where `name` = 'z3';

Non‑unique index scan: returns all rows matching a given value; it is a hybrid of index lookup and row scan.

range

explain select * from t1 where id between 1 and 3;
explain select * from t1 where id in (1,2,3);

Retrieves rows within a given range using an index. Suitable for BETWEEN , < , > , IN conditions. Faster than full table scan because it starts and ends at specific index points.

index

explain select id from t1;

Full index scan: only traverses the index tree, usually faster than ALL because the index file is smaller than the data file.

all

explain select * from t1;

Full table scan: traverses the entire table to find matching rows.

Note: Ensure queries achieve at least range level, preferably ref .

possible_keys

Shows one or more indexes that could be applied to the table. An index listed here may not be used by the optimizer.

key

The actual index used; NULL means no index was used. If a covering index is used, it appears only in the KEY column.

explain select col1, col2 from t1;
create index idx_col1_col2 on t1(col1, col2);
explain select col1, col2 from t1;

Case before adding the index:

Case after adding the index:

key_len

desc t1;
explain select * from t1 where col1 = 'ab';
explain select * from t1 where col1 = 'ab' and col2 = 'bc';

Shows the number of bytes used from the index. Shorter lengths are better, but the value reflects the maximum possible length, not the actual bytes read.

Summary: More conditions increase cost and key_len; keep key_len short for better efficiency.

key_len calculation rules

String

char(n): n bytes

varchar(n) (utf8): 3n + 2 bytes (extra 2 bytes store length)

Numeric

tinyint: 1 byte

smallint: 2 bytes

int: 4 bytes

bigint: 8 bytes

Time

date: 3 bytes

timestamp: 4 bytes

datetime: 8 bytes

If a column allows NULL, an extra byte records the null flag.

Maximum index length is 768 bytes; longer strings are indexed using a left‑prefix.

Rows

Estimates the number of rows the optimizer expects to read based on table statistics and index usage.

Extra

Contains additional important information not shown in other columns: id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra.

1. Using filesort

File sort operation.

2. Using temporary

ALTER TABLE `t1`
  ADD COLUMN `col1` VARCHAR(30) NULL DEFAULT NULL AFTER `other_column`;
ALTER TABLE `t1`
  ADD COLUMN `col2` VARCHAR(30) NULL DEFAULT NULL AFTER `col1`;

explain select col2 from t1 where col1 in ('ab','ac','as') group by col2 \G;
explain select col2 from t1 where col1 in ('ab','ac','as') group by col1, col2, col3 \G;

MySQL uses a temporary table to store intermediate results, common in ORDER BY and GROUP BY operations.

3. Using index

explain select col2 from t1 where col1=100;
explain select col1, col2 from t1;

Indicates the SELECT uses a covering index, avoiding table row access.

4. Using index condition

explain select * from tb_emp where `name` > 'z3';

The WHERE condition uses a range on a non‑covering index.

5. Using Where

Indicates that a WHERE filter is applied.

6. using join buffer

Shows that a join buffer is used.

7. impossible where

explain select * from t1 where 1=2;

The WHERE clause is always false, so no rows can be returned.

8. select table optimized away

When there is no GROUP BY, MySQL can optimize MIN/MAX or COUNT(*) using the index during the planning phase.

9. distinct

Optimizes DISTINCT by stopping after the first matching tuple is found.

Example

Example description:

explain select d1.name, (select id from t3) d2 from
(select id, name from t1 where other_column = '') d1
union
(select name, id from t2));

Query result:

First line (execution order 4): id = 1, the first SELECT of the UNION, select_type = primary, table column indicates the outer query.

Second line (id = 3): part of the third SELECT, derived because it appears in the FROM clause.

Third line (id = 2): subquery SELECT inside the SELECT list.

Fourth line (id = 1): select_type = union, the second SELECT of the UNION, executed first.

Fifth line (execution order 5): reading rows from the temporary UNION table; table column shows .

References

https://mysql.com

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html