Oracle 5.0, Reference Manual

Page: 685 / 3232

Optimizing
SELECT
Statements
665
OR (t1.a=t2.a AND t2.a IS NULL AND ...);
ref_or_null
[647] works by first doing a read on the reference key, and then a separate search for
rows with a
NULL
key value.
Note that the optimization can handle only one
IS NULL
[877] level. In the following query, MySQL
uses key lookups only on the expression
(t1.a=t2.a AND t2.a IS NULL)
and is not able to use
the key part on
b
:
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL)
OR (t1.b=t2.b AND t2.b IS NULL);
8.3.1.7.
LEFT JOIN
and
RIGHT JOIN
Optimization
MySQL implements an
A LEFT JOIN B join_condition
as follows:
• Table
B
is set to depend on table
A
and all tables on which
A
depends.
• Table
A
is set to depend on all tables (except
B
) that are used in the
LEFT JOIN
condition.
• The
LEFT JOIN
condition is used to decide how to retrieve rows from table
B
. (In other words, any
condition in the
WHERE
clause is not used.)
• All standard join optimizations are performed, with the exception that a table is always read after all
tables on which it depends. If there is a circular dependence, MySQL issues an error.
• All standard
WHERE
optimizations are performed.
• If there is a row in
A
that matches the
WHERE
clause, but there is no row in
B
that matches the
ON
condition, an extra
B
row is generated with all columns set to
NULL
.
• If you use
LEFT JOIN
to find rows that do not exist in some table and you have the following test:
col_name IS NULL
in the
WHERE
part, where
col_name
is a column that is declared as
NOT
NULL
, MySQL stops searching for more rows (for a particular key combination) after it has found one
row that matches the
LEFT JOIN
condition.
The implementation of
RIGHT JOIN
is analogous to that of
LEFT JOIN
with the roles of the tables
reversed.
The join optimizer calculates the order in which tables should be joined. The table read order forced by
LEFT JOIN
or
STRAIGHT_JOIN
helps the join optimizer do its work much more quickly, because there
are fewer table permutations to check. Note that this means that if you do a query of the following type,
MySQL does a full scan on
b
because the
LEFT JOIN
forces it to be read before
d
:
SELECT *
FROM a JOIN b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;
The fix in this case is reverse the order in which
a
and
b
are listed in the
FROM
clause:
SELECT *
FROM b JOIN a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;
For a
LEFT JOIN
, if the
WHERE
condition is always false for the generated
NULL
row, the
LEFT JOIN
is changed to a normal join. For example, the
WHERE
clause would be false in the following query if
t2.column1
were
NULL
:
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
Therefore, it is safe to convert the query to a normal join: