![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_1646196672.webp)
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
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...