MySQL EXPLAIN Deep Dive: Decoding Execution Plans for Query Optimization

Preparation

Many articles simply list MySQL execution plan concepts without concrete examples, making them hard to remember. This guide uses a hands-on approach with two tables: an order table ( t_order) and a customer table ( t_customer), linked by customer_id.

CREATE TABLE `t_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
  `order_no` int(11) DEFAULT NULL COMMENT 'Order Number',
  `customer_id` int(11) DEFAULT NULL COMMENT 'Customer ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert data
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 'Primary Key',
  `customer_name` varchar(255) DEFAULT NULL COMMENT 'Customer Name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

First Look at Execution Plan

Running EXPLAIN SELECT * FROM t_order returns multiple columns. The following image shows the raw output:

EXPLAIN SELECT * FROM t_order

Each column's meaning is summarized below:

Detailed Explanation of Execution Plan Columns

We now examine each column in detail.

1. id

Represents the execution order of SELECT statements. When ids are identical, execution proceeds top to bottom. A larger id value means higher priority and earlier execution. For subqueries, the id increments. The following example shows a subquery with id=2 executing first:

EXPLAIN SELECT (SELECT customer_id FROM t_order WHERE id = 1) FROM t_order der;

2. select_type

Indicates the query type: simple query, union, subquery, etc. Common values:

SIMPLE : No subqueries or UNION.

PRIMARY : The outermost SELECT containing complex subqueries.

UNION : For UNION/UNION ALL queries, the leftmost is PRIMARY; subsequent queries are UNION.

UNION RESULT : UNION deduplicates (UNION ALL does not), creating a temporary table; the query against that table has select_type UNION RESULT.

Example illustrating UNION:

EXPLAIN SELECT * FROM t_order WHERE id = 1
UNION SELECT * FROM t_order WHERE id = 2;

Other common types include SUBQUERY and DERIVED.

3. table

The queried table name (real name or alias) or special forms: <unionM,N>: Union query result; M and N are execution plan id values. <derivedN>: N is the derived table's id, originating from a subquery in the FROM clause. <subqueryN>: N is the materialized subquery result's id.

4. partitions

For partitioned tables, shows the partitions containing matching rows; otherwise NULL.

5. type

Indicates the join (access) type. Common types ordered from best to worst performance:

6. possible_keys and key

possible_keys lists indexes that might be used; the query may not actually use an index. If NULL, no index is used. key shows the index actually chosen by the optimizer, which may not be from possible_keys.

Example: add an index on t_order.customer_id:

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

Then query by customer_id:

The output shows both possible_keys and the actual key used.

7. key_len

Bytes used by the index; shorter is better.

8. ref

Shows which column of the index is used, possibly a constant.

9. rows

Estimated number of rows MySQL must examine to execute the query.

10. filtered

Percentage of rows satisfying the condition relative to the estimated rows; maximum is 100.

11. extra

Additional query information. Common values:

Using index : Covering index used.

Using Temporary : Temporary table used for intermediate results.

Using filesort : External sort used instead of index sort.

Other extra information can be found in the MySQL official documentation.