From: jon
Date: October 11 2006 6:29am
Subject: svn commit - mysqldoc@docsrva: r3599 - trunk/internals
List-Archive: http://lists.mysql.com/commits/13444
Message-Id: <200610110629.k9B6TSxQ031079@docsrva.mysql.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Author: jstephens
Date: 2006-10-11 08:29:28 +0200 (Wed, 11 Oct 2006)
New Revision: 3599
Log:
Adding Sergey P's new NULLs filtering writeup.
Modified:
trunk/internals/optimizer.xml
Modified: trunk/internals/optimizer.xml
===================================================================
--- trunk/internals/optimizer.xml 2006-10-11 06:18:16 UTC (rev 3598)
+++ trunk/internals/optimizer.xml 2006-10-11 06:29:28 UTC (rev 3599)
Changed blocks: 1, Lines Added: 132, Lines Deleted: 0; 4746 bytes
@@ -1738,7 +1738,139 @@
In this section, we discuss other, more specialized optimizations
performed in the MySQL server.
+
+
+ <literal>NULL</literal>s Filtering for <literal>ref</literal> and
+ <literal>eq_ref</literal> Access
+
+
+ This section discusses the NULLs filtering
+ optimization used for ref and
+ eq_ref joins.
+
+
+
+
+ Early <literal>NULL</literal>s 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 choosen,
+ 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.
+
+
+
+
+
+
+ Late <literal>NULL</literal>s 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 ). The
+ check itself is located in the function
+ join_read_always_key().
+
+
+
+
+
+