Understanding NULL Values and Their Impact on Indexes in MySQL

Preface – In MySQL a column without an explicit NOT NULL declaration receives a default NULL constraint, which can lead to uncertain query results and performance degradation when NULL values are used.

NULL handling operators – MySQL provides three operators for NULL handling: IS NULL , IS NOT NULL , and the spaceship operator <=> (which returns true when both operands are NULL). The function IFNULL() can replace NULL with a default value.

Example table and queries

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;
SELECT * FROM test_null WHERE name IS NULL;
SELECT * FROM test_null WHERE name IS NOT NULL;
SELECT * FROM test_null WHERE name <=> NULL;

The IS NULL predicate uses the index, while a plain equality comparison with = NULL returns no rows because the expression evaluates to NULL.

Aggregation and counting – COUNT(*) counts all rows, ignoring NULLs, whereas COUNT(column) counts only non‑NULL values, leading to different results. Functions like MAX() , MIN() , and other aggregates also ignore NULLs, which may produce unexpected outcomes.

Distinct, GROUP BY, ORDER BY – When using DISTINCT , GROUP BY , or ORDER BY , MySQL treats all NULL values as equal, so they appear as a single group or sort position.

Index usage with NULL columns – Contrary to some databases, MySQL can use indexes on columns that contain NULL values. Example:

SHOW CREATE TABLE sbtest1;
ALTER TABLE sbtest1 MODIFY k INT NULL, MODIFY c CHAR(120) NULL, MODIFY pad CHAR(60) NULL;
EXPLAIN SELECT id,k FROM sbtest1 WHERE k IS NULL;

The explain output shows the secondary index k_1 being used for the IS NULL condition.

Storage overhead – Each NULL value requires an extra byte in the row to store the NULL flag, which slightly increases storage size.

Recommendations – To avoid the pitfalls of NULL, define columns with NOT NULL and use sentinel values such as 0 for numbers or empty strings for text. When NULL cannot be avoided, handle it explicitly with IS NULL , IS NOT NULL , or IFNULL() to keep queries predictable and performant.