Why Avoid NULL Columns in MySQL? Performance and Storage Insights

Null values in MySQL often cause hidden performance and storage issues; this article analyzes the reasons and shows how to avoid them.

1. Why do many use NULL?

NULL is the default when creating tables, and many developers mistakenly think NOT NULL consumes more space; the real reason is convenience—no need to check for NULL during inserts.

2. Is it a myth?

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 complicate indexes, statistics, and values. When indexed, each record needs an extra byte, turning fixed‑size indexes into variable‑size ones.

3. Reasons to avoid NULL

All situations that use NULL can be represented by a meaningful default value, improving code readability and data integrity.

Updating a NULL column to NOT NULL cannot be done in‑place, leading to index fragmentation and performance loss.

NULL values in TIMESTAMP columns cause problems unless the explicit_defaults_for_timestamp option is enabled.

Negative conditions such as NOT IN or != with NULL always return empty result sets, making queries error‑prone.

NULL columns need an extra byte as a flag to indicate the NULL state.

Note: The performance benefit of converting NULL columns to NOT NULL is usually small; only change them if they cause concrete issues.

Example tables and queries illustrate the impact:

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);

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

-- Count ignores NULL values
select count(user_name) from table_3;

Index length calculations show how NULL adds storage overhead:

The same varchar(20) column in table_2 has a larger index length because the character set differs and one column is NULLable.

Key length depends on data type, character encoding, and NULLability: for utf8, key_len = 20*3 + 2; for utf8mb4, key_len = 20*4 + 1 + 2 (the extra 1 byte is the NULL flag).

Therefore, it is advisable to avoid NULL in indexed columns to keep indexes simple, reduce storage, and improve query performance.