Master MySQL EXPLAIN: Decode Execution Plans to Boost Query Performance

Introduction

Database performance tuning is essential for backend developers; MySQL's EXPLAIN command provides a detailed execution plan that helps evaluate query efficiency and guide optimization.

EXPLAIN Basics

When a SQL statement reaches the MySQL server, the optimizer analyzes it, possibly reorders operations, and generates an execution plan describing how data will be accessed (index scan, full table scan, etc.). Running EXPLAIN on a query returns a table with twelve columns that describe this plan.

explain SELECT * FROM user_info WHERE NAME='mufeng';

The result includes fields such as id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, and extra.

Key Columns Explained

id : Execution order of each SELECT; larger id means later execution, useful for nested queries.

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

table : Table name or alias involved in the step.

partitions : Partition information if the table is partitioned.

type : Access method – system, const, eq_ref, ref, ref_or_null, unique_subquery, index_subquery, range, index, all.

possible_keys : Indexes that the optimizer could consider.

key : Index actually chosen.

key_len : Length of the chosen index.

ref : Columns or constants used with the key.

rows : Estimated number of rows examined.

filtered : Approximate percentage of rows that match the condition.

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

Practical Analysis

A real‑world case shows a slow query on a large product_evaluation table despite an existing secondary index ( idx_evaluation_type). EXPLAIN reveals that MySQL chose the PRIMARY key instead of the secondary index, leading to full scans, temporary tables, and filesort, which caused the slowdown.

Because the secondary index is large and low‑cardinality, the optimizer prefers the primary key to avoid costly row lookups and sorting.

Forcing the use of the secondary index with FORCE INDEX(idx_product_id) can improve performance.

SELECT * FROM product_evaluation FORCE INDEX(idx_product_id)
WHERE product_id = 1 AND evaluation_type='GOOD'
ORDER BY id DESC LIMIT 200;

Conclusion

When encountering slow SQL, start with EXPLAIN to understand the actual execution plan. If a query lacks an appropriate index, add one; if an index exists but is not used, investigate why and consider forcing its use or rewriting the query.