7.4 Visual Explain Plan

The visual explain feature generates and displays a visual representation of the MySQL EXPLAIN statement by using extended information available in the extended JSON format. MySQL Workbench provides all of the EXPLAIN formats for executed queries including the raw extended JSON, traditional format, and visual query plan.

Visual Explain Usage

To view a visual explain execution plan, execute your query from the SQL editor and then select Execution Plan within the query results tab. The execution plan defaults to Visual Explain, but it also includes a Tabular Explain view that is similar to what you see when executing EXPLAIN in the MySQL client. For information about how MySQL executes statements, see Optimizing Queries with EXPLAIN.

Visual Explain Conventions

The order of execution in a visual explain diagram is bottom to top and left to right. The diagram examples that follow provide an overview of the graphic, textual, and informational conventions used to represent aspects of the visual explain plans. For specific information, see:

• Graphic Conventions

• Textual and Informational Conventions

The visual explain diagram in the first figure shows a visual representation of the following query.

SELECT CONCAT(customer.last_name, ', ', customer.first_name) 
  AS customer, address.phone, film.title FROM rental
INNER JOIN customer ON rental.customer_id = customer.customer_id
INNER JOIN address ON customer.address_id = address.address_id
INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
INNER JOIN film ON inventory.film_id = film.film_id
WHERE rental.return_date IS NULL
AND rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE()
LIMIT 5;

Figure 7.7 A Visual Explain Example

The next visual explain diagram shows the visual representation of a query that contains a hash join. Prior to MySQL Workbench 8.0.22, a hash join was represented by a block nested loop diamond for queries executed by MySQL 8.0.19 (or earlier).

SELECT first_name, last_name
FROM actor
FULL JOIN film_actor
WHERE '' = film_actor.actor_id;

Figure 7.8 A Visual Explain Example with a Hash Join

Graphic Conventions

• Standard Boxes: tables

• Rounded boxes: operations such as GROUP and SORT

• Framed boxes: subqueries

• Diamonds: joins

Textual and Informational Conventions

• Standard text below boxes: table (or alias) name

• Bold text below boxes: key/index that was used

• Number in top right of a box: number of rows used from the table after filtering

• Number in top left of a box: relative cost of accessing that table (requires MySQL 5.7 or higher)

• Number to the right of nested loop (or hash join) diamonds: number of rows produced by the JOIN

• Number above the diamonds: relative cost of the JOIN (requires MySQL 5.7 or higher)

The following table shows the associated colors and descriptions used in the visual explain diagram. For more information about cost estimates, see The Optimizer Cost Model.

Table 7.1 Visual Explain Diagram Information

System Name	Color	Text on Visual Diagram	Tooltip Related Information
SYSTEM	Blue	Single row: system constant	Very low cost
CONST	Blue	Single row: constant	Very low cost
EQ_REF	Green	Unique Key Lookup	Low cost -- The optimizer is able to find an index that it can use to retrieve the required records. It is fast because the index search directly leads to the page with all the row data
REF	Green	Non-Unique Key Lookup	Low-medium -- Low if the number of matching rows is small; higher as the number of rows increases
FULLTEXT	Yellow	Fulltext Index Search	Specialized FULLTEXT search. Low -- for this specialized search requirement
REF_OR_NULL	Green	Key Lookup + Fetch NULL Values	Low-medium -- if the number of matching rows is small; higher as the number of rows increases
INDEX_MERGE	Green	Index Merge	Medium -- look for a better index selection in the query to improve performance
UNIQUE_SUBQUERY	Orange	Unique Key Lookup into table of subquery	Low -- Used for efficient Subquery processing
INDEX_SUBQUERY	Orange	Non-Unique Key Lookup into table of subquery	Low -- Used for efficient Subquery processing
RANGE	Orange	Index Range Scan	Medium -- partial index scan
INDEX	Red	Full Index Scan	High -- especially for large indexes
ALL	Red	Full Table Scan	Very High -- very costly for large tables, but less of an impact for small ones. No usable indexes were found for the table, which forces the optimizer to search every row. This could also mean that the search range is so broad that the index would be useless.
UNKNOWN	Black	unknown	Note: This is the default, in case a match cannot be determined