This section discusses the
optimization used for
Suppose we have a join order such as this one:
..., tblX, ..., tblY, ...
Suppose further that table
eq_ref access on
tblY.key_column = tblX.column
or, in the case of
ref access using
multiple key parts, via
... AND tblY.key_partN = tblX.column AND ...
tblX.column can be
NULL. Here the early
NULLs filtering for
is applied. We make the following inference:
(tblY.key_partN = tblX.column) => (tblX.column IS NOT NULL)
The original equality can be checked only after we've read
the current rows of both tables
IS NOT NULL
predicate can be checked after we've read the current row of
tblX. If there are any tables in
the join order between
tblY, the added
NULL check will allow us to skip accessing those
This feature is implemented in these places in the server code:
refanalyzer (contained in such functions as
update_ref_and_keys()) detects and marks equalities like that shown above by setting
After the join order has been chosen,
IS NOT NULLpredicates to the conditions of the appropriate tables.
It is possible to add
IS NOT NULL
predicates for all equalities that could be used for
ref access (and not for those that are
actually used). However, this is currently not done.
Suppose we have a query plan with table
tblX being accessed via the
ref access method:
tblX.key_part1 = expr1 AND tblX.key_part2 = expr2 AND ...
Before performing an index lookup, we determine whether any
expri values is
NULL. If it is, we don't perform the
lookup, but rather immediately return that the matching
tuple is not found.
This optimization reuses the
null_rejecting attribute produced by the
NULLs filtering code (see
Section 18.104.22.168.1, “Early NULLs Filtering”). The
check itself is located in the function