MySQL - Explain

MySQL - The EXPLAIN Statement

The Role of the EXPLAIN Statement

The MySQL EXPLAIN statement is the single most important tool for understanding and optimizing query performance. It does not execute the query; instead, it provides a detailed report, called an execution plan, showing how MySQL intends to execute a SELECT, DELETE, INSERT, REPLACE, or UPDATE statement.

By analyzing this plan, you can identify performance bottlenecks, such as missing indexes, inefficient joins, or unnecessary full table scans. This allows you to make targeted improvements to your queries and table structures to significantly speed up execution time.

Note: EXPLAIN <table_name> is a synonym for DESCRIBE <table_name>. While useful, its primary purpose is to show table structure, not query execution. This tutorial focuses on EXPLAIN <query>.

Syntax

The basic syntax is simple:

EXPLAIN SELECT ... FROM ... WHERE ...;

Understanding the EXPLAIN Output

The output of EXPLAIN is a table with several columns. Here are the most critical ones to understand:

• id: A sequential identifier for each SELECT within the query.
• select_type: The type of SELECT (e.g., SIMPLE, JOIN, SUBQUERY).
• table: The table to which the row of output refers.
• type: This is crucial. It describes how tables are joined. The goal is to avoid ALL and aim for ref, eq_ref, range, or index.
• possible_keys: Shows which indexes MySQL could use to find rows.
• key: The actual index MySQL decided to use. If this is NULL, no index was used, which is often a red flag.
• rows: An estimate of the number of rows MySQL must examine to execute the query. Lower is better.
• Extra: Provides additional, critical information. Look for warnings like Using filesort or Using temporary, as they indicate costly operations.

Key type Values (from best to worst)

• system/const: The table has at most one matching row, which can be read at the start.
• eq_ref: Excellent. One row is read from this table for each combination of rows from the previous tables. Used for joins on primary or unique keys.
• ref: Good. All rows with matching index values are read. Used for joins on indexed columns that are not unique.
• range: Good. Only rows that are in a given range are retrieved, using an index.
• index: Scans the entire index tree. Faster than ALL but still not ideal.
• ALL: Terrible. A full scan of the entire table is performed. This is what you must try to avoid on large tables.

EXPLAIN ANALYZE for Actual Costs

Available since MySQL 8.0.18, EXPLAIN ANALYZE actually executes the query and provides an enhanced execution plan that includes actual timing and row counts, not just estimates. This is incredibly powerful for diagnosing where time is being spent.

EXPLAIN ANALYZE SELECT * FROM customers WHERE name = 'Alice Smith';

-- Example Output:
-> Filter: (customers.name = 'Alice Smith')  (cost=7.35 rows=1) (actual time=0.040..0.082 rows=1 loops=1)
    -> Table scan on customers  (cost=7.35 rows=70) (actual time=0.033..0.071 rows=70 loops=1)

The output shows the estimated cost/rows alongside the actual time and actual rows returned by each part of the plan.

Output Formats: TREE and JSON

You can change the output format for better readability or for programmatic analysis.

• FORMAT=TREE: Provides a hierarchical view of the execution plan, which is often easier to read for complex joins.
• FORMAT=JSON: Outputs a detailed JSON object, which is ideal for feeding into analysis tools.

-- Tree format is often the default for EXPLAIN ANALYZE
EXPLAIN FORMAT=TREE SELECT * FROM customers c JOIN orders o ON c.id = o.customer_id;

-- JSON format for detailed analysis
EXPLAIN FORMAT=JSON SELECT * FROM customers WHERE id = 1\G

A Practical Optimization Walkthrough

Let’s walk through optimizing a slow query. We’ll use the customers and orders tables from the INNER JOIN tutorial, but assume we forgot to create a crucial index.

Step 1: The Slow Query

Imagine the customer_id column in the orders table was created without an index:

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME NOT NULL,
    customer_id INT NOT NULL, -- No index on this column!
    amount DECIMAL(10, 2) NOT NULL
);

Step 2: Analyzing the Plan with EXPLAIN

We run EXPLAIN on our join query.

EXPLAIN SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE c.name = 'Alice Smith';

The simplified output for the orders table would look like this:

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	o	ALL	NULL	NULL	10000	Using where

The problem is immediately clear: type is ALL and key is NULL. MySQL is doing a full table scan on orders because customer_id is not indexed.

Step 3: Applying an Index

We fix this by adding an index on the foreign key column.

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Step 4: Re-analyzing the Plan

Now, we run the same EXPLAIN command again. The output for the orders table will be dramatically improved:

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	o	ref	idx_orders_customer_id	idx_orders_customer_id	2	Using where

Success! The type is now ref, and key shows our new index is being used. The estimated rows to be examined has dropped from 10,000 to just 2. The query will now be orders of magnitude faster.