MySQL Workbench Manual  /  Performance Tools  /  Tutorial: Using Visual Explain to Improve Query Performance

7.5 Tutorial: Using Visual Explain to Improve Query Performance

In this example, Visual Explain helps locate and fix problematic (slow) queries. This tutorial uses the DBT-3 database, and begins with the following query:

  SELECT * FROM orders
  WHERE YEAR(o_orderdate) = 1992 AND MONTH(o_orderdate) = 4
  AND o_clerk LIKE '%0223';

In the screenshot below, we executed this query and generated a Visual Explain report by selecting Query, Visual Explain Current Statement from the main menu.

Figure 7.8 DBT-3 Visual Explain Tutorial: Full Table Scan

DBT-3 Visual Explain Tutorial: Full Table Scan

Figure 7.9 DBT-3 Visual Explain Tutorial: Full Table Scan: Traditional View

DBT-3 Visual Explain Tutorial: Full Table Scan: Traditional View

Notice that ...

Why did this query generate a full table scan? Why is our indexed o_orderdate column missing as a possible key? Looking more closely, we notice that our indexed column is being used in an expression as "WHERE YEAR(o_orderdate) = 1992 AND MONTH(o_orderdate) = 4", so its index is not used. To use the existing index we can adjust the query like so:

  SELECT * FROM orders
  WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30'
  AND o_clerk LIKE '%0223';

Figure 7.10 DBT-3 Visual Explain Tutorial: Index Range Scan

DBT-3 Visual Explain Tutorial: Index Range Scan

Figure 7.11 DBT-3 Visual Explain Tutorial: Index Range Scan: Traditional View

DBT-3 Visual Explain Tutorial: Index Range Scan: Traditional View

Notice the differences. The Type changed from ALL to range, possible keys (and used key) changed from NULL to i_o_orderdate, and the number of scanned rows changed from 1.5 million to about 33 thousand. Still, scanning 33 thousand rows while returning just 18 is unnecessary, so we focus on the o_clerk column. An index here should improve performance: so:

CREATE INDEX i_o_clerk ON orders(o_clerk);

Figure 7.12 DBT-3 Visual Explain Tutorial: Index Range Scan: Traditional View, After Index

DBT-3 Visual Explain Tutorial: Index Range Scan: Traditional View, After Index

Notice that our new index is not being considered as a possible key. This is because we are searching the suffix of the o_clerk column, and indexes do not work with suffixes (although they do work with prefixes). In our simple case, we were being lazy and could have simply used the entire clerk ID. Adjusting the query shows better results:

SELECT * FROM orders
WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30'
AND o_clerk LIKE 'Clerk#000000223';

Figure 7.13 DBT-3 Visual Explain Tutorial: Index Range Scan: Better

DBT-3 Visual Explain Tutorial: Index Range Scan: Better

Figure 7.14 DBT-3 Visual Explain Tutorial: Index Range Scan: Better (Traditional)

DBT-3 Visual Explain Tutorial: Index Range Scan: Better (Traditional)

The new o_clerk index was considered and used, and our query scanned 1546 rows instead of 32642, and the query execution improved from 0.281 to 0.234 seconds. However, EXPLAIN estimates that this query scans 1546 rows to return 18. After reviewing our query, notice that a multi-column index can meet the conditions of our WHERE clause that is based on both the o_orderdate and o_clerk columns:

CREATE INDEX io_clerk_date ON orders(o_clerk, o_orderdate)
Note

We listed o_clerk as the first column in our index because o_orderdate uses a range. ...

Now, executing the same query shows even better results. An estimated 18 rows are both scanned and returned, and the execution time of our query is 0.234 seconds.

Figure 7.15 DBT-3 Visual Explain Tutorial: Index Range Scan: Best

DBT-3 Visual Explain Tutorial: Index Range Scan: Best

Figure 7.16 DBT-3 Visual Explain Tutorial: Index Range Scan: Best (traditional)

DBT-3 Visual Explain Tutorial: Index Range Scan: Best (Traditional)

To summarize the results:

Table 7.2 DBT-3 Visual Explain Tutorial Query Comparison

Type Possible keys Key Rows Scanned Duration (seconds) Extra info Rows returned
all NULL NULL 1.50M 1.201 Using where 18
range i_o_orderdate i_o_orderdate 32642 0.281 Using index condition; Using where 18
range i_o_orderdate, i_o_clerk i_o_clerk 1546 0.234 Using index condition; Using where 18
range i_o_orderdate, i_o_clerk, i_o_clerk_date i_o_clerk_date 18 0.234 Using index condition 18


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