7.3.1.1 Early NULLs Filtering

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.column can be NULL. Here the early NULLs 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 as update_ref_and_keys()) detects and marks equalities like that shown above by setting KEY_FIELD::null_rejecting=TRUE.

• After the join order has been chosen, add_not_null_conds() adds appropriate IS 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.