To optimize database performance, developers must understand how MySQL processes queries. The EXPLAIN statement reveals the execution plan chosen by the optimizer, detailing how tables are scanned, joined, and sorted. Each row in the output represents a specific step in the execution hierarchy.

Core Columns Overview

The output consists of several critical fields:

• id: Indicates the sequence of execution.
• select_type: Defines the complexity of the query (e.g., simple, subquery).
• table: The specific table being accessed.
• type: The join or access type, indicating performance efficiency.
• possible_keys: Lists indexes that could potentially be used.
• key: The actual index selected by the optimizer.
• key_len: The length of the key used.
• ref: Shows which columns or constants are compared to the index.
• rows: An estimate of the number of rows to be examined.
• filtered: The percentage of rows filtered by table conditions.
• Extra: Additional information about how the query is resolved.

Execution Order (id)

The id column determines the priority of execution steps:

• If id values are identical, execution proceeds from top to bottom.
• If id values differ, higher numbers are executed first.
• If id is NULL, the step is executed last, typically involving derived tables or union results.

Table References and Derived Tables

The table column shows the target table. When a subquery exists in the FROM clause, MySQL may create a temporary result set known as a derived table.

Derived tables are managed primarily at the server layer. The server parses the SQL, optimizes the plan, and materializes the subquery result into a temporary structure. If the result set is large, the storage engine (e.g., InnoDB) handles the physical storage on disk. These tables exist only for the duration of the query and are destroyed immediately after execution.

Consider the following execution plan involving a derived table:

+----+-------------+--------------+-------+---------------+---------+---------+------+--------+-----------------------------+
| id | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows   | Extra                       |
+----+-------------+--------------+-------+---------------+---------+---------+------+--------+-----------------------------+
|  1 | PRIMARY     | <derived2>   | ALL   | NULL          | NULL    | NULL    | NULL | 5      |                             |
|  2 | DERIVED     | customers    | range | status_idx    | status_idx | 5    | NULL | 5      | Using where; Using filesort |
+----+-------------+--------------+-------+---------------+---------+---------+------+--------+-----------------------------+

In this scenario, id=2 generates the derived table from the customers table. The primary query (id=1) then scans this temporary result set.

Query Types (select_type)

This field categorizes the query structure:

• SIMPLE: A straightforward SELECT without subqueries or UNION operations. Note that join queries can still be classified as SIMPLE.
• PRIMARY: The outermost query in a complex statement involving unions or subqueries.
• SUBQUERY: The first SELECT statement within a subquery that does not depend on the outer query.
• DEPENDENT SUBQUERY: A subquery that relies on values from the outer query.
• DERIVED: A subquery located in the FROM clause.
• UNION: The second or subsequent SELECT statement within a UNION operation.
• UNION RESULT: Represents the final result set of a UNION operation.

Example of a dependent subquery:

EXPLAIN SELECT u.name, 
(SELECT i.title FROM inventory i WHERE i.cid = u.customer_id) 
FROM customers u;

Example of a derived table:

EXPLAIN SELECT * FROM (SELECT * FROM customers WHERE account_level >= 5) AS active_users;

MySQL may optimize derived tables by merging them into the main query. If merging occurs, the EXPLAIN output will show SIMPLE instead of DERIVED, avoiding the overhead of temporary table creation.

Access Types (type)

The type column is critical for performance tuning, ranked from best to worst:

• system: The table contains exactly one row (system table).
• const: The query matches a single row using a PRIMARY KEY or UNIQUE index with a constant value.
• eq_ref: Used for joins where a PRIMARY KEY or UNIQUE index is matched against a specific value from another table.
• ref: Non-unique index lookup. Multiple rows may match the index value.
• range: Retrieves rows within a specific range using an index (e.g., using >, <, or BETWEEN).
• index: Performs a full scan of the index tree. This is faster than a full table scan if the index is covering.
• ALL: Full table scan. This is the least efficient method and should be avoided for large tables.

Index Details (key, key_len, ref)

The key column shows the actual index chosen. If NULL, no index was used. The key_len indicates the number of bytes used from the index. This is useful for verifying composite index usage.

Calculation nuances include:

• CHAR(n): Uses n bytes.
• INT: Typically 4 bytes.
• VARCHAR: Depends on character set. For UTF-8, its roughly 3 * n + 2 bytes (2 bytes for length storage).
• Nullability: If the column allows NULL, an additional byte is used to store the null status.

The ref column displays the column or constant used to compare against the selected index.

Row Estimates (rows, filtered)

The rows field estimates how many records the engine must examine to find the relevant data. Lower numbers generally indicate better performance. The filtered percentage represents how many of those examined rows pass the table conditions.

Extra Information

The Extra column provides context on how the query is resolved:

• Using index: Indicates a covering index is used; the server retrieves data directly from the index without accessing the table heap.
• Using where: The server layer is filtering rows after the storage engine retrieves them. This does not necessarily mean an index is unused, but that post-retrieval filtering is occurring.
• Using index condition: Indicates Index Condition Pushdown (ICP) is active, where conditions are pushed down to the storage engine to reduce heap access.
• Using filesort: MySQL must perform an additional pass to sort the results. This often suggests a missing index on the ORDER BY columns.