Why You Should Avoid NULL in MySQL: Performance and Design Insights

1. Why is NULL so widely used?

NULL is the default when creating tables, so inexperienced or lazy developers often leave it.

Many believe NOT NULL consumes more space, which is not the main issue.

Developers think NULL makes data insertion and SQL writing more convenient.

2. Is it a myth?

MySQL official documentation:

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 finds it hard to optimize queries on nullable columns; they complicate indexes, index statistics, and values. A nullable column adds an extra byte for the NULL flag, and when indexed it can turn fixed‑size indexes into variable‑size ones.

— from "High Performance MySQL, 2nd Edition"

3. Reasons to avoid NULL

(1) Every situation that uses NULL can be represented by a meaningful default value, improving code readability, maintainability, and enforcing business data rules.

(2) Updating a NULL value to a non‑NULL cannot be done in‑place, leading to index fragmentation and performance loss.

Note: The performance benefit of converting a NULL column to NOT NULL is usually small; only change it if you have proven issues.

(3) NULL in TIMESTAMP columns can cause problems if explicit_defaults_for_timestamp is not enabled.

(4) Negative conditions such as NOT IN or != return empty results when NULL values are present, making queries error‑prone.

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

-- 2. Single‑column index does not store NULL, composite index does not store rows where all indexed columns are NULL
select * from table_3 where name != 'zhaoliu_2_1';

-- 3. CONCAT with NULL yields NULL
select CONCAT('1', null) from dual; -- returns NULL

-- 4. COUNT on a NULL column ignores NULL values
select count(user_name) from table_3; -- returns 2

-- 5. Comparison with = NULL gives wrong results; use IS NULL instead
select * from table_3 where user_name is null;

(5) A NULL column needs an extra byte as a flag to indicate NULL.

Key length calculation depends on data type, character set, and NULLability:

For varchar(20) with utf8 (3 bytes per character) the index length is 20*3 + 2 = 62 bytes.

For utf8mb4 (4 bytes per character) plus the NULL flag, the length is 20*4 + 1 + 2 = 83 bytes.

In summary, nullable columns increase storage, complicate indexing and statistics, and can cause subtle bugs; using NOT NULL with appropriate default values is generally the better practice.