Suppose we have a join order such as this one:
..., tblX, ..., tblY, ...
Suppose further that table tblY
is accessed
via ref
or 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 ...
where
tblX.
can
be column
NULL
. Here the early
NULL
s filtering for ref
(or eq_ref
) access 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 tblX
and
tblY
. The IS NOT NULL
predicate can be checked after we've read the current row of
table tblX
. If there are any tables in the
join order between tblX
and
tblY
, the added IS NOT
NULL
check will allow us to skip accessing those
tables.
This feature is implemented in these places in the server code:
The
ref
analyzer (contained in such functions asupdate_ref_and_keys()
) detects and marks equalities like that shown above by settingKEY_FIELD::null_rejecting=TRUE
.After the join order has been chosen,
add_not_null_conds()
adds appropriateIS NOT NULL
predicates 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.