{"id":9890,"date":"2020-08-20T01:12:53","date_gmt":"2020-08-20T08:12:53","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=9890"},"modified":"2023-10-23T06:28:51","modified_gmt":"2023-10-23T13:28:51","slug":"mysql-is-null-optimization","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-index\/mysql-is-null-optimization\/","title":{"rendered":"MySQL IS NULL Optimization"},"content":{"rendered":"\n

Summary:<\/strong> in this tutorial, you will learn how to optimize queries that involve the IS NULL condition.<\/p>\n\n\n\n

Setting up table structure and index<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

First, create a table called persons<\/code> with three columns id<\/code>, name<\/code>, and age<\/code>:<\/p>\n\n\n

CREATE TABLE persons (\r\n    id INT PRIMARY KEY,\r\n    name VARCHAR(50),\r\n    age INT,\r\n    INDEX age_index (age)\r\n);<\/code><\/span><\/pre>\n\n\n
The persons<\/code> table has an index on the age column with the name age_index<\/code>.<\/p>\n\n\n\n
Second, insert some rows into the persons<\/code> table:<\/p>\n\n\n
INSERT INTO persons (id, name, age) \nVALUES\r\n(1<\/span>, 'John Doe'<\/span>, 30<\/span>),\r\n(2<\/span>, 'Jane Smith'<\/span>, NULL<\/span>),\r\n(3<\/span>, 'Michael Johnson'<\/span>, 35<\/span>),\r\n(4<\/span>, 'Sarah Williams'<\/span>, 28<\/span>),\r\n(5<\/span>, 'Robert Brown'<\/span>, NULL<\/span>),\r\n(6<\/span>, 'Emily Davis'<\/span>, 29<\/span>),\r\n(7<\/span>, 'David Lee'<\/span>, NULL<\/span>),\r\n(8<\/span>, 'Olivia Clark'<\/span>, 24<\/span>),\r\n(9<\/span>, 'James Taylor'<\/span>, 31<\/span>),\r\n(10<\/span>, 'Emma Anderson'<\/span>, 27<\/span>);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL IS NULL optimization example <\/h2>\n\n\n\n
The following query retrieves rows from the persons<\/code> table with the values in the age<\/code> column are NULL<\/code>:<\/p>\n\n\n
SELECT * FROM persons\nWHERE age IS NULL<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+--------------+------+\r\n| id | name         | age  |\r\n+----+--------------+------+\r\n|  2<\/span> | Jane Smith   | NULL<\/span> |\r\n|  5<\/span> | Robert Brown | NULL<\/span> |\r\n|  7<\/span> | David Lee    | NULL<\/span> |\r\n+----+--------------+------+\r\n3<\/span> rows in set (0.02<\/span> sec)\r<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL uses the index<\/a> when it searches for NULL<\/code> with the IS NULL<\/code> operator as shown in the following EXPLAIN query:<\/p>\n\n\n
EXPLAIN<\/span> \nSELECT<\/span> \n  * \nFROM<\/span> \n  persons \nWHERE<\/span> \n  age IS<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+-------------+---------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+\r\n| id | select_type | table   | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                 |\r\n+----+-------------+---------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+\r\n|  1<\/span> | SIMPLE      | persons | NULL       | ref  | age_index     | age_index | 5<\/span>       | const<\/span> |    3<\/span> |   100.00<\/span> | Using index condition |\r\n+----+-------------+---------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+\r\n1<\/span> row in<\/span> set<\/span>, 1 warning (0.02 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL can also optimize for the combination col = value OR col IS NULL<\/code>, see the following example:<\/p>\n\n\n
EXPLAIN<\/span> \nSELECT<\/span> \n  * \nFROM<\/span> \n  persons \nWHERE<\/span> \n  age = 12<\/span> \n  OR<\/span> age IS<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+----+-------------+---------+------------+-------------+---------------+-----------+---------+-------+------+----------+-----------------------+\r\n| id | select_type | table   | partitions | type        | possible_keys | key       | key_len | ref   | rows | filtered | Extra                 |\r\n+----+-------------+---------+------------+-------------+---------------+-----------+---------+-------+------+----------+-----------------------+\r\n|  1<\/span> | SIMPLE      | persons | NULL       | ref_or_null | age_index     | age_index | 5<\/span>       | const<\/span> |    4<\/span> |   100.00<\/span> | Using index condition |\r\n+----+-------------+---------+------------+-------------+---------------+-----------+---------+-------+------+----------+-----------------------+\r\n1<\/span> row in<\/span> set<\/span>, 1 warning (0.01 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the EXPLAIN<\/code> shows ref_or_null<\/code> when the optimization is applied.<\/p>\n\n\n\n
Sometimes, MySQL might not effectively use indexes with complex OR<\/code> conditions. For example:<\/p>\n\n\n
EXPLAIN<\/span> \nSELECT<\/span> \n  * \nFROM<\/span> \n  persons \nWHERE<\/span> \n  name<\/span> = \"Jane Smith\"<\/span>\n  OR<\/span> age IS<\/span> NULL<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, MySQL could not leverage the index:<\/p>\n\n\n
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |\r\n+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n|  1<\/span> | SIMPLE      | persons | NULL<\/span>       | ALL  | age_index     | NULL<\/span> | NULL<\/span>    | NULL<\/span> |   10<\/span> |    19.00<\/span> | Using where |\r\n+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+\r\n1<\/span> row in set, 1<\/span> warning (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • MySQL searches for an index on the column that uses the IS NULL to optimize the query.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t