Oracle 5.0, Справочное руководство

Страница: 669 / 3232

EXPLAIN
Output Format
649
No row satisfies the condition for a query such as
SELECT MIN(...) FROM ... WHERE
condition
.
•
no matching row in const table
For a query with a join, there was an empty table or a table with no rows satisfying a unique index
condition.
•
No tables used
The query has no
FROM
clause, or has a
FROM DUAL
clause.
•
Not exists
MySQL was able to do a
LEFT JOIN
optimization on the query and does not examine more rows
in this table for the previous row combination after it finds one row that matches the
LEFT JOIN
criteria. Here is an example of the type of query that can be optimized this way:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
WHERE t2.id IS NULL;
Assume that
t2.id
is defined as
NOT NULL
. In this case, MySQL scans
t1
and looks up the rows
in
t2
using the values of
t1.id
. If MySQL finds a matching row in
t2
, it knows that
t2.id
can
never be
NULL
, and does not scan through the rest of the rows in
t2
that have the same
id
value.
In other words, for each row in
t1
, MySQL needs to do only a single lookup in
t2
, regardless of how
many rows actually match in
t2
.
•
Range checked for each record (index map: N)
MySQL found no good index to use, but found that some of indexes might be used after column
values from preceding tables are known. For each row combination in the preceding tables, MySQL
checks whether it is possible to use a
range
[647] or
index_merge
[647] access method
to retrieve rows. This is not very fast, but is faster than performing a join with no index at all. The
applicability criteria are as described in
Section 8.3.1.3, “Range Optimization” , and Section 8.3.1.4,
“Index Merge Optimization” , with the exception that all column values for the preceding table are
known and considered to be constants.
Indexes are numbered beginning with 1, in the same order as shown by
SHOW INDEX
for the table.
The index map value
N
is a bitmask value that indicates which indexes are candidates. For example,
a value of
0x19
(binary 11001) means that indexes 1, 4, and 5 will be considered.
•
Select tables optimized away
The query contained only aggregate functions (
MIN()
[971] ,
MAX()
[971] ) that were all
resolved using an index, or
COUNT(*)
[970] for
MyISAM
, and no
GROUP BY
clause. The optimizer
determined that only one row should be returned.
•
unique row not found
For a query such as
SELECT ... FROM tbl_name
, no rows satisfy the condition for a
UNIQUE
index or
PRIMARY KEY
on the table.
•
Using filesort
MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done
by going through all rows according to the join type and storing the sort key and pointer to the row for
all rows that match the
WHERE
clause. The keys then are sorted and the rows are retrieved in sorted
order. See Section 8.3.1.11, “
ORDER BY
Optimization” .
•
Using index