MySQL IS NULL Optimization

Summary: in this tutorial, you will learn how to optimize queries that involve the IS NULL condition.

Setting up table structure and index

Before optimizing queries that involve the IS NULL, you need to have an index on the column that you intend to use with the IS NULL condition because indexing can significantly enhance the query performance.

First, create a table called persons with three columns id, name, and age:

CREATE TABLE persons (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX age_index (age)
);

The persons table has an index on the age column with the name age_index.

Second, insert some rows into the persons table:

INSERT INTO persons (id, name, age) 
VALUES
(1, 'John Doe', 30),
(2, 'Jane Smith', NULL),
(3, 'Michael Johnson', 35),
(4, 'Sarah Williams', 28),
(5, 'Robert Brown', NULL),
(6, 'Emily Davis', 29),
(7, 'David Lee', NULL),
(8, 'Olivia Clark', 24),
(9, 'James Taylor', 31),
(10, 'Emma Anderson', 27);Code language: PHP (php)

MySQL IS NULL optimization example

The following query retrieves rows from the persons table with the values in the age column are NULL:

SELECT * FROM persons
WHERE age IS NULL;Code language: PHP (php)

Output:

+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  2 | Jane Smith   | NULL |
|  5 | Robert Brown | NULL |
|  7 | David Lee    | NULL |
+----+--------------+------+
3 rows in set (0.02 sec)
Code language: PHP (php)

MySQL uses the index when it searches for NULL with the IS NULL operator as shown in the following EXPLAIN query:

EXPLAIN 
SELECT 
  * 
FROM 
  persons 
WHERE 
  age IS NULL;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-------------+---------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | persons | NULL       | ref  | age_index     | age_index | 5       | const |    3 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.02 sec)Code language: JavaScript (javascript)

MySQL can also optimize for the combination col = value OR col IS NULL, see the following example:

EXPLAIN 
SELECT 
  * 
FROM 
  persons 
WHERE 
  age = 12 
  OR age IS NULL;
Code language: SQL (Structured Query Language) (sql)

Output:

+----+-------------+---------+------------+-------------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type        | possible_keys | key       | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------------+---------------+-----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | persons | NULL       | ref_or_null | age_index     | age_index | 5       | const |    4 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------------+---------------+-----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)Code language: JavaScript (javascript)

In this example, the EXPLAIN shows ref_or_null when the optimization is applied.

Sometimes, MySQL might not effectively use indexes with complex OR conditions. For example:

EXPLAIN 
SELECT 
  * 
FROM 
  persons 
WHERE 
  name = "Jane Smith"
  OR age IS NULL;Code language: SQL (Structured Query Language) (sql)

In this example, MySQL could not leverage the index:

+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | persons | NULL       | ALL  | age_index     | NULL | NULL    | NULL |   10 |    19.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)Code language: PHP (php)

Summary

• MySQL searches for an index on the column that uses the IS NULL to optimize the query.