In this tutorial, we will learn about the IS NULL value optimization in the MySQL table when the index is present for the specified column. This is more like an information tutorial instead of examples and demonstrations. So, let’s get started!
Also read: MySQL Prefix Index with Examples
What is IS NULL
While inserting the values into the table, there can be scenarios when you are not certain of a particular value and if you don’t insert the value into the column, it will become NULL in the table.
If the column is not set as a primary key or doesn’t have a NOT NULL constraint, it can hold the NULL values.
MySQL INDEX IS NULL Optimization
When there are NULL values in the table, MySQL performs the same optimization on the column in the same way it does for the non-null values.
For example, Using IS NULL, MySQL can search for NULL values using indexes and ranges.
Let’s take an example.
Consider the following table schema and the table data-
Here, we have some NULL values in the dept column.
Also, we have created an index on the dept column already.
Now, let’s run a query to find the records where the value of dept is null.
SELECT * FROM students WHERE dept IS NULL;Code language: SQL (Structured Query Language) (sql)
The above query does use the index when finding the null values.
Let’s check by using the EXPLAIN clause.
EXPLAIN SELECT * FROM students WHERE dept IS NULL;Code language: SQL (Structured Query Language) (sql)
As you can see, mysql uses the index to find the data.
MySQL also uses the index to optimize the combination of col=value OR col IS NULL.
EXPLAIN SELECT * FROM students WHERE dept="CS" OR dept IS NULL;Code language: SQL (Structured Query Language) (sql)
As you can see, the query uses the index for the combination as well.
In the above output, you can see the ‘ref_or_null’ which is displayed when the optimization for is null is done.
When using ref_or_null, it reads the reference key first and then does a separate search for rows with a NULL key value.
Summary
In this tutorial, we learned about the MySQL index IS NULL optimization and how it works. MySQL takes care of the optimization for NULL values internally without letting you worried. If you think this tutorial helped you, don’t forget to share it with your friends!
References
MySQL official documentation on is-null optimzation.