MySQL Workbench

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.

Note

The extended EXPLAIN format is available as of MySQL server 5.6.5.

MySQL Workbench provides all of the EXPLAIN formats for executed queries including the raw extended JSON, traditional format, and visual query plan.

Visual Explain Conventions

The Visual Explain diagram in next figure shows the 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 order of execution is bottom to top, and left to right.

Graphic Conventions

  • Standard Boxes: tables

  • Rounded boxes: operations such as GROUP and SORT

  • Framed boxes: subqueries

  • Diamonds: joins

Textual 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 greater)

  • Number to the right of nested loop diamonds: number of rows produced by the JOIN

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

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 NameColorText on Visual DiagramTooltip related information
SYSTEMBlueSingle row: system constantVery low cost
CONSTBlueSingle row: constantVery low cost
EQ_REFGreenUnique Key LookupLow 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
REFGreenNon-Unique Key LookupLow-medium -- Low if the number of matching rows is small; higher as the number of rows increases
FULLTEXTYellowFulltext Index SearchSpecialized FULLTEXT search. Low -- for this specialized search requirement
REF_OR_NULLGreenKey Lookup + Fetch NULL ValuesLow-medium -- if the number of matching rows is small; higher as the number of rows increases
INDEX_MERGEGreenIndex MergeMedium -- look for a better index selection in the query to improve performance
UNIQUE_SUBQUERYOrangeUnique Key Lookup into table of subqueryLow -- Used for efficient Subquery processing
INDEX_SUBQUERYOrangeNon-Unique Key Lookup into table of subqueryLow -- Used for efficient Subquery processing
RANGEOrangeIndex Range ScanMedium -- partial index scan
INDEXRedFull Index ScanHigh -- especially for large indexes
ALLRedFull Table ScanVery 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.
UNKNOWNBlackunknownNote: This is the default, in case a match cannot be determined

Visual Explain Usage

To view a visual explain execution plan, execute your query from the SQL editor and then choose the Execution Plan tab in the query results tab. The execution plan defaults to "Visual Explain" but also has a "Tabular Explain" view that is similar to what you would see when executing EXPLAIN in the MySQL client.