Understanding and Analyzing MySQL Execution Plans

To grasp MySQL execution plans, it helps to first understand the database’s three‑layer architecture: the application layer (client interaction, connection handling, and result return), the logic layer (query processing, transaction management, etc.), and the physical layer (actual files on disk such as data and log files). All major relational databases follow a similar structure.

When a SQL statement is received, MySQL allocates a thread, the query processor optimizes the statement, generates an execution plan, and passes it to the plan executor, which interacts with the transaction and storage managers to retrieve data from the physical layer and finally returns the result to the application layer.

The execution plan is essential for assessing query performance; examining it reveals how MySQL will access data and which indexes it will use.

How to Analyze an Execution Plan

MySQL provides the EXPLAIN keyword to display a query’s execution plan. The output consists of several columns, each conveying specific information about how the optimizer intends to execute the statement.

id : Identifier of the SELECT operation, indicating the order of execution.

select_type : Type of SELECT, such as SIMPLE, PRIMARY, UNION, DEPENDENT UNION, UNION RESULT, SUBQUERY, DEPENDENT SUBQUERY, or DERIVED.

table : The table referenced by the row.

partitions : If the query involves a partitioned table, this shows which partitions are accessed.

type : Join type, ordered from best to worst: system, const, eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL. Generally, a plan should achieve at least range and preferably ref .

possible_keys : Indexes that MySQL could use to find rows in the table.

key : The actual index chosen; NULL if no index is used.

key_len : Length of the chosen index; shorter is better when precision is not lost.

ref : Column or constant used with the key to select rows.

rows : Estimated number of rows MySQL must examine.

filtered : Estimated percentage of rows that pass the table’s filter conditions.

Extra : Additional details such as: Distinct – stops after finding the first matching row. Select tables optimized away – no table or index scan needed. Not exists – LEFT JOIN optimization. Range checked for each record – partial index usage. Using filesort – requires an extra pass to sort results. Using index – data retrieved solely from the index. Using temporary – a temporary table is created. Using where – WHERE clause applied to filter rows. Using index for group‑by – index covers GROUP BY or DISTINCT.

By interpreting these columns, developers can identify performance bottlenecks, choose better indexes, and rewrite queries for optimal execution.