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.

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

An example Visual Explain diagram:

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

A Visual Explain Example

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 associated colors and descriptions used in the Visual Explain diagram:

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

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.


User Comments
Sign Up Login You must be logged in to post a comment.