Why NULL Columns Can Break MySQL Indexes and How to Avoid It

Background

In MySQL a column that is not explicitly declared NOT NULL receives an implicit NULL constraint. Many developers accept this default for convenience, but it introduces uncertainty in queries and can degrade database performance.

NULL handling operators

MySQL provides three operators for NULL values: IS NULL , IS NOT NULL , and the spaceship operator <=> . It also offers the function IFNULL() which returns a second argument when the first is NULL.

IS NULL

returns true when the column value is NULL. IS NOT NULL returns true when the column value is not NULL. <=> behaves like equality but returns true when both operands are NULL. IFNULL(a,b) returns b if a is NULL, otherwise a.

Practical example

Creating a simple table and inserting rows with and without 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;

The result shows one row with a NULL name. Queries using WHERE name = NULL return an empty set, while WHERE name IS NULL correctly returns the row with the NULL value.

Index usage with NULL columns

MySQL can use indexes on columns that contain NULL values, unlike Oracle. The following demonstration uses the sysbench test tables:

SHOW CREATE TABLE sbtest1\G
-- output shows a PRIMARY KEY on `id` and a secondary index `k_1` on column `k`
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 id=100001;   -- uses PRIMARY KEY
EXPLAIN SELECT id,k FROM sbtest1 WHERE k IS NULL; -- uses secondary index `k_1`

Both queries demonstrate that MySQL can leverage indexes even when the indexed column holds NULL values.

Effect on aggregate functions

When a column contains NULL, aggregate functions such as COUNT(*) and COUNT(col) behave differently: COUNT(*) counts all rows, while COUNT(col) ignores NULLs. This can lead to misleading statistics.

SELECT COUNT(*), COUNT(name) FROM test_null;
-- Returns 2 and 1 respectively because the NULL row is excluded from COUNT(name)

Impact on DISTINCT, GROUP BY, ORDER BY

MySQL treats NULL values as equal for DISTINCT, GROUP BY, and ORDER BY. Consequently, multiple NULL rows collapse into a single group, which may affect sorting and grouping results.

INSERT INTO test_null VALUES (3,NULL);
SELECT DISTINCT name FROM test_null;   -- returns 'zlm' and NULL (single NULL row)
SELECT GROUP BY name FROM test_null;   -- same effect
SELECT * FROM test_null ORDER BY name; -- NULL rows appear first

Storage overhead

Each NULL value requires an extra byte in the row to store the NULL flag. This overhead is independent of the column's data type.

Recommendations

Because of the uncertainties and performance penalties introduced by NULL columns, it is advisable to define columns as NOT NULL and use sentinel values such as 0 for numeric types or empty strings for text types instead of relying on NULL.

When NULL handling is unavoidable, use IS NULL / IS NOT NULL for predicates and IFNULL() to provide default values, keeping the SQL logic explicit and easier to maintain.