Understanding MySQL EXPLAIN Output and Query Optimization

MySQL performs query optimization by generating an execution plan with EXPLAIN, which first creates a cost‑based plan and then executes it. The article introduces the purpose of EXPLAIN and shows how to view its output.

Key columns in the EXPLAIN result are explained:

id : identifier for each SELECT in the query.

select_type : describes the type of SELECT (e.g., SIMPLE, PRIMARY, UNION, SUBQUERY, DEPENDENT SUBQUERY, DERIVED, MATERIALIZED).

table : the table name accessed.

type : access method (system, const, eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL).

possible_keys and key : indexes that could be used and the actual index used.

key_len : length of the used index.

ref : column or constant used for index lookup.

rows : estimated number of rows examined.

filtered : percentage of rows filtered after applying conditions.

Extra : additional information such as "Using where", "Using index", "Using join buffer (Block Nested Loop)", "LooseScan", etc.

The article provides numerous EXPLAIN examples with SQL statements and their output tables, illustrating how different queries (simple SELECT, joins, subqueries, UNION, derived tables) affect the execution plan.

It also covers advanced concepts like subquery materialization, semi‑join strategies (Table pullout, DuplicateWeedout, LooseScan, FirstMatch), index merge types (Intersection, Union, Sort‑Union), and how MySQL uses temporary tables and filesort.

Throughout, code snippets are presented within ... tags to show exact MySQL commands and results, helping readers understand and apply optimization techniques to improve SQL performance.