(mysql.info.gz) IS NULL optimization
Info Catalog
(mysql.info.gz) Index Merge optimization
(mysql.info.gz) Query Speed
(mysql.info.gz) DISTINCT optimization
7.2.7 How MySQL Optimizes `IS NULL'
-----------------------------------
MySQL can do the same optimization on COL_NAME `IS NULL' that it can do
with COL_NAME `=' CONSTANT_VALUE. For example, MySQL can use indexes
and ranges to search for `NULL' with `IS NULL'.
SELECT * FROM TBL_NAME WHERE KEY_COL IS NULL;
SELECT * FROM TBL_NAME WHERE KEY_COL <=> NULL;
SELECT * FROM TBL_NAME
WHERE KEY_COL=CONST1 OR KEY_COL=CONST2 OR KEY_COL IS NULL;
If a `WHERE' clause includes a COL_NAME `IS NULL' condition for a
column that is declared as `NOT NULL', that expression will be
optimized away. This optimization does not occur in cases when the
column might produce `NULL' anyway; for example, if it comes from a
table on the right side of a `LEFT JOIN'.
MySQL 4.1.1 and up can additionally optimize the combination `COL_NAME
= EXPR AND COL_NAME IS NULL', a form that is common in resolved
subqueries. `EXPLAIN' will show `ref_or_null' when this optimization
is used.
This optimization can handle one `IS NULL' for any key part.
Some examples of queries that are optimized, assuming that there is an
index on columns `a' and `b' of table `t2':
SELECT * FROM t1 WHERE t1.a=EXPR OR t1.a IS NULL;
SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;
SELECT * FROM t1, t2
WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;
SELECT * FROM t1, t2
WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
OR (t1.a=t2.a AND t2.a IS NULL AND ...);
`ref_or_null' works by first doing a read on the reference key, and
then a separate search for rows with a `NULL' key value.
Note that the optimization can handle only one `IS NULL' level. In the
following query, MySQL will use key lookups only on the expression
`(t1.a=t2.a AND t2.a IS NULL)' and not be able to use the key part on
`b':
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL)
OR (t1.b=t2.b AND t2.b IS NULL);
Info Catalog
(mysql.info.gz) Index Merge optimization
(mysql.info.gz) Query Speed
(mysql.info.gz) DISTINCT optimization
automatically generated byinfo2html