Oracle 5.0, Reference Manual

Page: 667 / 3232

EXPLAIN
Output Format
647
ref
[646] can be used for indexed columns that are compared using the
=
or
<=>
operator. In the
following examples, MySQL can use a
ref
[646] join to process
ref_table
:
SELECT * FROM ref_table WHERE key_column = expr ;
SELECT * FROM ref_table , other_table
WHERE ref_table . key_column = other_table . column ;
SELECT * FROM ref_table , other_table
WHERE ref_table . key_column_part1 = other_table . column
AND ref_table . key_column_part2 =1;
•
fulltext
[647]
The join is performed using a
FULLTEXT
index.
•
ref_or_null
[647]
This join type is like
ref
[646] , but with the addition that MySQL does an extra search for rows that
contain
NULL
values. This join type optimization is used most often in resolving subqueries. In the
following examples, MySQL can use a
ref_or_null
[647] join to process
ref_table
:
SELECT * FROM ref_table
WHERE key_column = expr OR key_column IS NULL;
See Section 8.3.1.6, “
IS NULL
Optimization” .
•
index_merge
[647]
This join type indicates that the Index Merge optimization is used. In this case, the
key
column in the
output row contains a list of indexes used, and
key_len
contains a list of the longest key parts for
the indexes used. For more information, see Section 8.3.1.4, “Index Merge Optimization” .
•
unique_subquery
[647]
This type replaces
ref
[646] for some
IN
subqueries of the following form:
value IN (SELECT primary_key FROM single_table WHERE some_expr )
unique_subquery
[647] is just an index lookup function that replaces the subquery completely
for better efficiency.
•
index_subquery
[647]
This join type is similar to
unique_subquery
[647] . It replaces
IN
subqueries, but it works for
nonunique indexes in subqueries of the following form:
value IN (SELECT key_column FROM single_table WHERE some_expr )
•
range
[647]
Only rows that are in a given range are retrieved, using an index to select the rows. The
key
column
in the output row indicates which index is used. The
key_len
contains the longest key part that was
used. The
ref
column is
NULL
for this type.
range
[647] can be used when a key column is compared to a constant using any of the
=
[875] ,
<>
[876] ,
>
[876] ,
>=
[876] ,
<
[876] ,
<=
[876] ,
IS NULL
[877] ,
<=>
[876] ,
BETWEEN
[877] , or
IN()
[878] operators:
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;