MySQL Workbench Manual  /  Performance Tools  /  Visual Explain Plan

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:

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

Content is described in the surrounding text.

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

Content is described in the surrounding text.

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