- MySQL 5.1 Reference Manual :: 7 Optimization :: 7.2 Optimizing SELECT and Other Statements :: 7.2.8 IS NULL Optimization
-
- MySQL 5.1 Reference Manual
- Preface, Notes, Licenses
- 1 General Information
- 2 Installing and Upgrading MySQL
- 3 Tutorial
- 4 MySQL Programs
- 5 MySQL Server Administration
- 6 Backup and Recovery
- 7 Optimization
- 8 Language Structure
- 9 Internationalization and Localization
- 10 Data Types
- 11 Functions and Operators
- 12 SQL Statement Syntax
- 13 Storage Engines
- 14 High Availability and Scalability
- 15 MySQL Enterprise Monitor
- 16 Replication
- 17 MySQL Cluster NDB 6.X/7.X
- 18 Partitioning
- 19 Stored Programs and Views
- 20 INFORMATION_SCHEMA Tables
- 21 Connectors and APIs
- 22 Extending MySQL
- A MySQL 5.1 Frequently Asked Questions
- B Errors, Error Codes, and Common Problems
- C MySQL Change History
- D Restrictions and Limits
- Index
- Standard Index
- C Function Index
- Command Index
- Function Index
- INFORMATION_SCHEMA Index
- Transaction Isolation Level Index
- JOIN Types Index
- Operator Index
- Option Index
- Privileges Index
- SQL Modes Index
- Status Variable Index
- Statement/Syntax Index
- System Variable Index
MySQL can perform the same optimization on
col_name
IS NULL
that it can use forcol_name
=
constant_value
. For example, MySQL can use indexes and ranges to search forNULL
withIS NULL
.Examples:
SELECT * FROM
tbl_name
WHEREkey_col
IS NULL; SELECT * FROMtbl_name
WHEREkey_col
<=> NULL; SELECT * FROMtbl_name
WHEREkey_col
=const1
ORkey_col
=const2
ORkey_col
IS NULL;If a
WHERE
clause includes acol_name
IS NULL
condition for a column that is declared asNOT NULL
, that expression is optimized away. This optimization does not occur in cases when the column might produceNULL
anyway; for example, if it comes from a table on the right side of aLEFT JOIN
.MySQL can also optimize the combination
, a form that is common in resolved subqueries.col_name
=expr
ORcol_name
IS NULLEXPLAIN
showsref_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
andb
of tablet2
: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 aNULL
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 onb
:SELECT * FROM t1, t2 WHERE (t1.a=t2.a AND t2.a IS NULL) OR (t1.b=t2.b AND t2.b IS NULL);