Why MySQL NULL Can Still Use Indexes and How It Affects Query Performance

NULL is a special constraint for columns; if a column is not explicitly defined with the NOT NULL keyword, MySQL adds a NULL constraint by default, which can cause uncertainty in queries and degrade database performance.

Preface

Many developers lazily accept MySQL's default of allowing NULL values, but this habit leads to ambiguous query results and performance issues when NULL is involved.

Introduce

MySQL provides three operators to handle NULL values: IS NULL , IS NOT NULL , and the spaceship operator <=> , plus the function IFNULL() . IS NULL : returns true if the column value is NULL. IS NOT NULL : returns true if the column value is not NULL. <=> : a comparison operator similar to = but returns true when both operands are NULL (e.g., NULL <=> NULL is true). IFNULL(a, b) : returns b when a is NULL, otherwise returns a . It is analogous to Oracle's NVL() .

Operators

IS NULL

IS NOT NULL

<=>

IFNULL

Example

Creating a table and inserting data with NULL values:

create table test_null(
  id int not null,
  name varchar(10)
);
insert into test_null values(1,'zlm');
insert into test_null values(2,null);
select * from test_null;

Query results:

+----+------+
| id | name |
+----+------+
|  1 | zlm  |
|  2 | NULL |
+----+------+

Using different predicates:

select * from test_null where name=null;      -- returns empty set
select * from test_null where name is null;  -- returns the row with NULL
select * from test_null where name is not null; -- returns the non‑NULL row
select * from test_null where null=null;      -- returns empty set
select * from test_null where null<>null;    -- returns empty set
select * from test_null where null<=>null;   -- returns both rows (true)

These tests show that IS NULL can use an index, contrary to the common belief that columns containing NULL invalidate indexes.

Aggregate Functions

Counting rows demonstrates the difference between COUNT(*) and COUNT(column):

select count(*), count(name) from test_null;
-- result: count(*) = 2, count(name) = 1

NULL values are ignored by COUNT(column), which can lead to inaccurate statistics if the column contains many NULLs.

Distinct, Group By, Order By

When using DISTINCT, GROUP BY, or ORDER BY, all NULL values are treated as the same value, which may affect sorting and grouping results.

select distinct name from test_null;
-- returns two rows: 'zlm' and NULL

select name from test_null group by name;
-- returns two rows: NULL and 'zlm'

select id, name from test_null order by name;
-- NULL rows appear first

Index Usage on NULL Columns

MySQL can use indexes on columns that contain NULL values, unlike Oracle where such indexes become ineffective. The following example shows that a secondary index on a nullable column is still used:

alter table sbtest1 modify k int null, modify c char(120) null, modify pad char(60) null;
insert into sbtest1 values(100001,null,null,null);
explain select id,k from sbtest1 where k is null;
-- type: ref, key: k_1, using index condition

Recommendations

Because NULL values introduce many uncertainties—affecting query results, aggregate calculations, sorting, and requiring extra handling with IFNULL() —it is advisable not to define columns with a default NULL. Instead, declare columns NOT NULL and use a sentinel value such as 0 for numeric types or an empty string for character types.

By avoiding default NULLs, developers can reduce technical debt, simplify SQL logic, and improve overall database performance.