![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_1646196670.webp)
EXPLAIN
Output Format
650
The column information is retrieved from the table using only information in the index tree without
having to do an additional seek to read the actual row. This strategy can be used when the query
uses only columns that are part of a single index.
If the
Extra
column also says
Using where
, it means the index is being used to perform lookups
of key values. Without
Using where
, the optimizer may be reading the index to avoid reading data
rows but not using it for lookups. For example, if the index is a covering index for the query, the
optimizer may scan it without using it for lookups.
•
Using index for group-by
Similar to the
Using index
table access method,
Using index for group-by
indicates that
MySQL found an index that can be used to retrieve all columns of a
GROUP BY
or
DISTINCT
query
without any extra disk access to the actual table. Additionally, the index is used in the most efficient
way so that for each group, only a few index entries are read. For details, see
Section 8.3.1.12,
“
GROUP BY
Optimization”
.
•
Using sort_union(...)
,
Using union(...)
,
Using intersect(...)
These indicate how index scans are merged for the
index_merge
[647]
join type. See
Section 8.3.1.4, “Index Merge Optimization”
.
•
Using temporary
To resolve the query, MySQL needs to create a temporary table to hold the result. This typically
happens if the query contains
GROUP BY
and
ORDER BY
clauses that list columns differently.
•
Using where
A
WHERE
clause is used to restrict which rows to match against the next table or send to the client.
Unless you specifically intend to fetch or examine all rows from the table, you may have something
wrong in your query if the
Extra
value is not
Using where
and the table join type is
ALL
or
index
[648]
. Even if you are using an index for all parts of a
WHERE
clause, you may see
Using
where
if the column can be
NULL
.
•
Using where with pushed condition
This item applies to
NDBCLUSTER
tables only. It means that MySQL Cluster is using the Condition
Pushdown optimization to improve the efficiency of a direct comparison between a nonindexed
column and a constant. In such cases, the condition is “pushed down” to the cluster's data nodes
and is evaluated on all data nodes simultaneously. This eliminates the need to send nonmatching
rows over the network, and can speed up such queries by a factor of 5 to 10 times over cases where
Condition Pushdown could be but is not used. For more information, see
Section 8.3.1.5, “Engine
Condition Pushdown Optimization”
.
EXPLAIN
Output Interpretation
You can get a good indication of how good a join is by taking the product of the values in the
rows
column of the
EXPLAIN
output. This should tell you roughly how many rows MySQL must examine
to execute the query. If you restrict queries with the
max_join_size
[469]
system variable, this row
product also is used to determine which multiple-table
SELECT
statements to execute and which to
abort. See
Section 8.9.2, “Tuning Server Parameters”
.
The following example shows how a multiple-table join can be optimized progressively based on the
information provided by
EXPLAIN
.
Suppose that you have the
SELECT
statement shown here and that you plan to examine it using
EXPLAIN
:
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
Содержание 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 ...