Understanding MySQL EXPLAIN Execution Plans: A Detailed Guide

MySQL's EXPLAIN statement simulates the execution of a SQL query and returns detailed information that helps developers analyze and improve query performance.

To illustrate the concepts, two sample tables are created: t_order (order information) and t_customer (customer information). Sample data is inserted into both tables.

CREATE TABLE `t_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_no` int(11) DEFAULT NULL COMMENT '订单号',
  `customer_id` int(11) DEFAULT NULL COMMENT '客户id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_order` VALUES ('1','1001','1');
INSERT INTO `t_order` VALUES ('2','1002','26');

CREATE TABLE `t_customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `customer_name` varchar(255) DEFAULT NULL COMMENT '客户姓名 ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_customer` VALUES ('1','John');
INSERT INTO `t_customer` VALUES ('2','Tom');

Running EXPLAIN SELECT * FROM t_order produces a result set with many columns. Each column is explained in detail:

id : Execution order of SELECT statements; larger values are executed earlier. Sub‑queries receive incremented ids.

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

table : The table name or alias involved; may show special values like <unionM,N> , <derivedN> , or <subqueryN> .

partitions : Shows the partition(s) accessed for partitioned tables; NULL otherwise.

type : Join type, ordered from best to worst (e.g., system, const, eq_ref, ref, range, index, all).

possible_keys and key : Indexes that could be used and the index actually chosen by the optimizer.

key_len : Number of bytes used from the chosen index (shorter is better).

ref : Column or constant compared with the index.

rows : Estimated number of rows examined.

filtered : Approximate percentage of rows that satisfy the condition.

extra : Additional information such as Using index , Using temporary , or Using filesort .

To see the impact of indexes, an index on customer_id is added:

ALTER TABLE `t_order`
ADD INDEX `idx_customer` (`customer_id`) USING BTREE;

After adding the index, an EXPLAIN of a query that filters by customer_id shows the new index in the key column, demonstrating how MySQL chooses the most efficient execution plan.

References: MySQL official documentation and related articles on query optimization.