Why Avoid NULL in MySQL? Performance, Indexing, and Best Practices

Null values are a common source of headaches, as seen with Java's NullPointerException, prompting languages like Java 8 to introduce Optional for safer handling.

In MySQL, many optimization guides recommend using NOT NULL, yet they rarely explain the underlying reasons; this article examines why NULL is used and presents arguments against it.

Why do so many people use NULL?

NULL is the default when creating tables, and inexperienced or lazy developers often overlook it. Some mistakenly believe NOT NULL consumes more space, but the real issue is that NULL allows developers to insert data without explicit checks, making SQL writing feel easier.

Is it misinformation?

NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.

MySQL struggles to optimize queries on nullable columns; they make indexes, index statistics, and values more complex. Nullable columns need extra storage and special handling. When indexed, each record requires an extra byte, turning fixed-size indexes into variable-size ones. — From "High Performance MySQL, 2nd Edition"

Thus, the concern is not a myth; there is solid theoretical backing.

Give me a reason not to use NULL?

1. Every situation that uses NULL can be represented by a meaningful value, improving code readability, maintainability, and data integrity through constraints.

2. Updating a NULL column to a non‑NULL value cannot be done in‑place, leading to index splits and performance degradation.

3. NULL values in TIMESTAMP columns can cause issues, especially when the explicit_defaults_for_timestamp parameter is not enabled.

4. Negative conditions like NOT IN or != produce empty results when NULLs are present, making queries error‑prone.

Example:

create table table_2 (
    `id` INT(11) NOT NULL,
    user_name varchar(20) NOT NULL
);

create table table_3 (
    `id` INT(11) NOT NULL,
    user_name varchar(20)
);

insert into table_2 values (4,"zhaoliu_2_1"),(2,"lisi_2_1"),(3,"wangmazi_2_1"),
    (1,"zhangsan_2"),(2,"lisi_2_2"),(4,"zhaoliu_2_2"),(3,"wangmazi_2_2");

insert into table_3 values (1,"zhaoliu_2_1"),(2, null);

-- 1. NOT IN subquery returns empty when NULL exists
select user_name from table_2 where user_name not in (
    select user_name from table_3 where id!=1
);

The query returns zero rows because the NULL in table_3 makes the NOT IN condition always evaluate to UNKNOWN.

Additional points:

Single‑column indexes do not store NULL values; composite indexes omit rows where all indexed columns are NULL, leading to unexpected result sets.

When concatenating fields, any NULL operand causes the whole result to be NULL.

COUNT on a nullable column ignores NULLs, so statistics can be misleading.

Comparisons using = NULL are always false; proper checks require IS NULL or IS NOT NULL.

Further code demonstrates how NULL columns affect index length:

alter table table_3 add index idx_user_name (user_name);
alter table table_2 add index idx_user_name (user_name);
explain select * from table_2 where user_name='zhaoliu_2_1';
explain select * from table_3 where user_name='zhaoliu_2_1';

Even with identical varchar(20) definitions, table_2 shows a larger key_len because its character set differs and one column is nullable. The calculation of key_len depends on data type, character encoding, and the NULL flag (e.g., 62 = 20*3 + 2, 83 = 20*4 + 1 + 2).

Therefore, it is advisable to avoid NULL in indexed columns, as NULL adds storage overhead, complicates index statistics, and can degrade query performance.