EXPLAIN EXTENDED
Output Format
652
mysql>
ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
Now
tt.ActualPC
and
et.EMPLOYID
are both
VARCHAR(15)
. Executing the
EXPLAIN
statement
again produces this result:
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC, NULL NULL NULL 3872 Using
ClientID, where
ActualPC
do ALL PRIMARY NULL NULL NULL 2135
Range checked for each record (index map: 0x1)
et_1 ALL PRIMARY NULL NULL NULL 74
Range checked for each record (index map: 0x1)
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
This is not perfect, but is much better: The product of the
rows
values is less by a factor of 74. This
version executes in a couple of seconds.
A second alteration can be made to eliminate the column length mismatches for the
tt.AssignedPC
= et_1.EMPLOYID
and
tt.ClientID = do.CUSTNMBR
comparisons:
mysql>
ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
->
MODIFY ClientID VARCHAR(15);
After that modification,
EXPLAIN
produces the output shown here:
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using
ClientID, where
ActualPC
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
At this point, the query is optimized almost as well as possible. The remaining problem is that, by
default, MySQL assumes that values in the
tt.ActualPC
column are evenly distributed, and that is
not the case for the
tt
table. Fortunately, it is easy to tell MySQL to analyze the key distribution:
mysql>
ANALYZE TABLE tt;
With the additional index information, the join is perfect and
EXPLAIN
produces this result:
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC NULL NULL NULL 3872 Using
ClientID, where
ActualPC
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
Note that the
rows
column in the output from
EXPLAIN
is an educated guess from the MySQL join
optimizer. You should check whether the numbers are even close to the truth by comparing the
rows
product with the actual number of rows that the query returns. If the numbers are quite different, you
might get better performance by using
STRAIGHT_JOIN
in your
SELECT
statement and trying to list the
tables in a different order in the
FROM
clause.
It is possible in some cases to execute statements that modify data when
EXPLAIN SELECT
is used
with a subquery; for more information, see
Section 13.2.9.8, “Subqueries in the
FROM
Clause”
.
8.2.3.
EXPLAIN EXTENDED
Output Format
When
EXPLAIN
is used with the
EXTENDED
keyword, the output includes a
filtered
column not
otherwise displayed. This column indicates the estimated percentage of table rows that will be filtered
by the table condition. In addition, the statement produces extra information that can be viewed by
issuing a
SHOW WARNINGS
statement following the
EXPLAIN
statement. The
Message
value in
SHOW WARNINGS
output displays how the optimizer qualifies table and column names in the
SELECT
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
