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, from the main menu.
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';
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
column. An index here should improve performance: so:
CREATE INDEX i_o_clerk ON orders(o_clerk);
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';
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
CREATE INDEX io_clerk_date ON orders(o_clerk, o_orderdate)
o_clerk as the first column in our
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.
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|
|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|