Understanding MySQL EXPLAIN: Syntax, Output Columns, and Query Optimization Techniques

The EXPLAIN statement in MySQL prefixes a query to generate an execution plan without actually running the query (except for subqueries in the FROM clause, which are executed to build temporary tables).

EXPLAIN helps identify performance bottlenecks by showing the order of table access, join types, possible and chosen indexes, estimated rows, and extra information such as whether a temporary table or filesort is used.

Extended Forms

EXPLAIN EXTENDED provides additional details; after execution, SHOW WARNINGS reveals the optimizer‑rewritten query and estimated row counts for joins.

EXPLAIN PARTITIONS displays which partitions of a partitioned table may be accessed.

Important Tips

1. The result reflects the current data in the tables; 2. Different MySQL versions may produce different plans because optimizer strategies evolve.

Column Descriptions

id : Sequential identifier of each SELECT step; larger values execute later.

select_type : Indicates query type (simple, primary, subquery, derived, union, etc.).

table : Shows which table (or derived/union temporary) the row refers to.

type : Access type ordered from best to worst (null, system, const, eq_ref, ref, range, index, ALL). Aim for at least ref or range.

possible_keys : Indexes that could be used; may be null if the optimizer deems indexes unnecessary.

key : The actual index chosen; null means a full scan.

key_len : Number of bytes of the index used.

ref : Columns or constants used with the chosen index.

rows : Estimated number of rows examined.

Extra : Additional notes such as using index, using where, using temporary, using filesort, etc., which are crucial for tuning.

Optimization Recommendations

If type shows ALL, create appropriate indexes to avoid full table scans.

Watch for Using temporary (often caused by GROUP BY without suitable indexes) and Using filesort (sorting not covered by an index); add or reorder indexes accordingly.

When Using where appears with a full scan, an index on the WHERE columns can improve performance.

Index Usage Analysis and Examples

Examples demonstrate how index order affects the plan, how range conditions may or may not use an index depending on data distribution, and how ORDER BY can leverage or ignore indexes.

Key takeaways include: the leftmost‑prefix rule for composite indexes, the impact of data values on index effectiveness, and the benefit of covering indexes to eliminate extra lookups.

Summary

Understanding each EXPLAIN column enables developers to spot inefficient scans, unnecessary temporary tables, and costly filesorts, and to apply targeted indexing strategies that follow the leftmost‑prefix principle for both WHERE and ORDER BY clauses.