DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(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