(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