Suppose we have a join order such as this one:
..., tblX, ..., tblY, ...
Suppose further that table
tblY is accessed
tblY.key_column = tblX.column
or, in the case of
ref access using
multiple key parts, via
... AND tblY.key_partN = tblX.column AND ...
NULL. Here the early
NULLs filtering for
eq_ref) access is applied. We make the
(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.