7.2.6.1 NULLs Filtering for ref and eq_ref Access

[+/-]

7.2.6.1.1 Early NULLs Filtering

7.2.6.1.2 Late NULLs Filtering

This section discusses the NULLs filtering optimization used for ref and eq_ref joins.

7.2.6.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.

7.2.6.1.2 Late NULLs Filtering

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 of the 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 early NULLs filtering code (see Section 7.2.6.1.1, “Early NULLs Filtering”). The check itself is located in the function join_read_always_key().