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.
EXPLAIN format is available as
of MySQL server 5.6.5.
MySQL Workbench provides all of the
for executed queries including the raw extended JSON, traditional
format, and visual query plan.
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;
The order of execution is bottom to top, and left to right.
Standard Boxes: tables
Rounded boxes: operations such as GROUP and SORT
Framed boxes: subqueries
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|
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.