5.2.5 How MySQL Optimises IS NULL

MySQL can do the same optimization on column IS NULL as it can do with column = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

SELECT * FROM table_name WHERE key_col IS NULL;

SELECT * FROM table_name WHERE key_col <=> NULL;

SELECT * FROM table_name WHERE key_col=# OR key_col=# OR key_col IS NULL

If you use column_name IS NULL on a NOT NULL in a WHERE clause on table that is not used OUTER JOIN that expression will be optimised away.

MySQL 4.1.1 can additionally optimise the combination column = expr AND column IS NULL, an form that is common in resolved sub queries. 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 optimised (assuming key on t2 (a,b)):

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 after that a separate search after rows with NULL key.

Note that the optimization can only handle one IS NULL level.

SELECT * FROM t1,t2 where (t1.a=t2.a AND t2.a IS NULL) OR (t1.b=t2.b AND t2.b IS NULL);

Int the above case MySQL will only use key lookups on the part (t1.a=t2.a AND t2.a IS NULL) and not be able to use the key part on b.

User Comments

Add your own comment.