Understanding MySQL EXPLAIN: Detailed Explanation of Execution Plan Fields

Explain Overview

The EXPLAIN keyword is commonly used in MySQL to view the execution plan of a SQL statement without actually running the query, allowing quick identification of performance issues.

Test Tables Creation

First, three tables are created for demonstration: user , role , and role_user .

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `age` int(11) NOT NULL DEFAULT 0,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` (`id`, `name`, `age`,`update_time`) VALUES
(1,'张三',23,'2020-12-22 15:27:18'),
(2,'李四',24,'2020-06-21 15:27:18'),
(3,'王五',25,'2020-07-20 15:27:18');

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `role` (`id`, `name`) VALUES
(1,'产品经理'),(2,'技术经理'),(3,'项目总监');

DROP TABLE IF EXISTS `role_user`;
CREATE TABLE `role_user` (
  `id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_role_user_id` (`role_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `role_user` (`id`, `role_id`, `user_id`) VALUES
(1,2,1),(2,1,2),(3,3,3);

Running EXPLAIN

Example query:

EXPLAIN SELECT * FROM user WHERE id = 2;

The resulting table contains twelve columns, each describing a different aspect of the execution plan.

id Field

The id column indicates the order in which MySQL executes the SELECT statements; larger ids are executed earlier. When ids are equal, they belong to the same execution group.

select_type Field

The select_type column shows the type of SELECT, such as SIMPLE , PRIMARY , SUBQUERY , DERIVED , UNION , and UNION RESULT . Each type is explained with examples.

table Field

Indicates which table (or derived table) the row refers to, e.g., user , role_user , or <union2,3> for UNION results.

type Field

Describes how MySQL accesses rows. Common values are system , const , eq_ref , ref , range , index , and ALL , ordered from best to worst performance.

possible_keys, key, key_len Fields

possible_keys lists indexes that could be used; key shows the index actually chosen; key_len indicates the number of bytes of the index used.

ref, rows, filtered Fields

ref shows the columns or constants compared with the index. rows is an estimate of rows examined, and filtered is the percentage of rows that pass the filter.

Extra Field

Provides additional information such as Using index , Using where , Using temporary , Using filesort , Using join buffer , Impossible where , and Select tables optimized away . Each indicates specific optimization or inefficiency details.

Practical Demonstration

(1) Querying EXPLAIN SELECT * FROM user WHERE name = '张三'; shows that the name column has no index.

ALTER TABLE user ADD INDEX index_name_age_time (name, age, update_time);

After adding the composite index, the EXPLAIN output shows the index being used.

(2) The "left‑most prefix" rule must be followed when creating composite indexes.

(3) Avoid applying functions to indexed columns (e.g., LEFT(name,2) ) because it disables index usage.

(4) Avoid using != , <> , IS NULL , LIKE '%...' patterns that prevent index utilization.

For more details, refer to the linked articles at the end of the original document.

—If this article helped you, please share it with your friends.