Important statistics collected for the executed query, including time, temporary tables, indexes, joins, etc., are collected in the "Query Statistics SQL Editor Results" tab (see the following two images).
Enable queries and collect performance schema statistics.
performance_schema Statement detection enabled.
The Visual Explanation feature creates and presents a visual description of a MySQL EXPLAIN statement by leveraging additional data contained in the enhanced JSON format. All EXPLAIN formats, including standard format, raw extended JSON, and visual query plans, can be used to execute queries in MySQL Workbench.
Execute the query in the SQL Editor and select Execution Procedure from the query results menu to receive a visual description of the execution plan. In addition to the default visual explain view, the execution plan also contains a tabular explain view, similar to what you see when you run EXPLAIN in the MySQL client. For more information about how MySQL executes statements, see Optimizing Queries Using EXPLAIN.
Visual explanation convention
Visual explanation diagrams should be read from bottom to top, left to right. An overview of the graphical, textual, and informational conventions used to represent the various elements of a visual interpretation plan is given in the diagram example below. For more details, see -
Graphics Standard
Information and text conventions
Visual explanation of the first graph The graph provides a visual description of the following query.
select * FROM employee_table
Standard box: table
Rounded corner box: GROUP and SORT operations
Frame Box: Subquery
Diamond: Connection
Standard text below box: Table (or alias) name
box: Key/index used
The number in the upper right corner of the box: the number of rows used in the filtered table
The number in the upper left corner of the box: the relative cost of accessing the table (requires MySQL 5.7 or higher)
The number on the right side of the nested loop (or hash join) diamond: the number of rows generated by the JOIN
Number above diamond: relative cost of JOIN (requires MySQL 5.7 or higher)
The following table shows the relevant colors and descriptions used in visual interpretation diagrams. For more information about cost estimates, see Optimizer Cost Model.
system name |
color |
Text on visualization chart |
Tooltip related information |
|---|---|---|---|
system |
blue |
Single line: system constant |
The cost is very low |
constant |
blue |
Single line: constant |
The cost is very low |
EQ_REF |
green |
Unique key lookup |
Low Cost - The optimizer is able to find an index that can be used to retrieve the required records. It's fast because the index search points directly to the page containing all rows of data |
refer to |
green |
Non-unique key lookup |
Low-Medium -- Low if there are fewer matching rows; higher as the number of rows increases |
full text |
yellow |
Full text index search |
Dedicated full-text search. Low - for this specialized search requirement |
REF_OR_NULL |
green |
Search by key to get NULL value |
Low-medium - if the number of matched rows is small; higher as the number of rows increases |
INDEX_MERGE |
green |
Index merge |
Medium - Looking for better index choices in queries to improve performance |
UNIQUE_SUBQUERY |
orange color |
Unique key lookup in subquery table |
Low -- for efficient subquery processing |
INDEX_SUBQUERY |
orange color |
Non-unique key lookup in subquery table |
Low -- for efficient subquery processing |
scope |
orange color |
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 high cost for large tables, but less impact for small tables. No free index is found for the table, forcing the optimizer to search every row. It may also mean that the search is too broad and the index will be useless. |
unknown |
black |
unknown |
Note: This is the default value in case a match cannot be determined |
In this article, we learned how to get query statistics in MySQL Workbench and what different characteristics it has at the same time.
The above is the detailed content of Query statistics in MySQL. For more information, please follow other related articles on the PHP Chinese website!