(mysql.info) is-null-optimization

Info Catalog

(mysql.info) index-merge-optimization

(mysql.info) query-speed

(mysql.info) distinct-optimization

 
 7.2.7 `IS NULL' Optimization
 ----------------------------
 
 MySQL can perform the same optimization on COL_NAME `IS NULL' that it
 can use for COL_NAME `=' CONSTANT_VALUE.  For example, MySQL can use
 indexes and ranges to search for `NULL' with `IS NULL'.
 
 Examples:
 
      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 is 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 can also optimize the combination `COL_NAME = EXPR AND COL_NAME
 IS NULL', a form that is common in resolved subqueries.  `EXPLAIN'
 shows `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 uses key lookups only on the expression
 `(t1.a=t2.a AND t2.a IS NULL)' and is not 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) index-merge-optimization

(mysql.info) query-speed

(mysql.info) distinct-optimization