Why You Should Avoid NULL Columns in MySQL: Performance, Index, and Logic Pitfalls

Background

Null values are a common source of bugs in many programming languages (e.g., Java's NullPointerException) and in relational databases. While most MySQL performance guides advise using NOT NULL whenever possible, they rarely explain the underlying reasons.

Why Developers Use NULL

Many developers accept NULL because it is the default column definition, they assume it saves effort when inserting data, or they mistakenly believe it consumes less space.

Is the Concern 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.

According to the MySQL documentation and the book High Performance MySQL (2nd edition) , NULL columns increase storage, make index statistics more complex, and force MySQL to perform special handling during queries.

Concrete Reasons to Avoid NULL

Every situation that uses NULL can be expressed with a meaningful default value, improving code readability and data integrity.

Updating a NULL column to a non‑NULL value cannot be done in‑place, leading to index fragmentation and degraded performance.

Timestamp columns with NULL cause errors unless the explicit_defaults_for_timestamp parameter is enabled.

Negative conditions such as NOT IN or != return an empty result set when NULL values are present, easily producing wrong query results.

Each NULL column adds an extra byte as a flag, increasing row size.

Worked Example

create table table_2 (id INT(11) NOT NULL, user_name varchar(20) NOT NULL);</code>
<code>create table table_3 (id INT(11) NOT NULL, user_name varchar(20));</code>
<code>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');</code>
<code>insert into table_3 values (1,'zhaoliu_2_1'),(2,null);</code>
<code>-- 1. NOT IN returns empty because of NULL</code>
<code>select user_name from table_2 where user_name not in (select user_name from table_3 where id!=1);</code>
<code>-- Result: 0 rows</code>
<code>-- 2. Single‑column index does not store NULL, composite index does not store rows where all indexed columns are NULL</code>
<code>select * from table_3 where name!='zhaoliu_2_1';</code>
<code>-- 3. CONCAT with NULL yields NULL</code>
<code>select CONCAT('1',null) from dual; -- returns NULL</code>
<code>-- 4. COUNT ignores NULL</code>
<code>select count(user_name) from table_3; -- returns 2</code>
<code>-- 5. Equality test with NULL is wrong; use IS NULL</code>
<code>select * from table_3 where user_name = null; -- returns 0 rows</code>
<code>select * from table_3 where user_name is null; -- returns the row with NULL

Index Length Impact

The following images show that a VARCHAR(20) column that allows NULL produces a larger index length.

Key length calculation:

NOT NULL varchar(20) utf8: key_len = 20*3 + 2 = 62 NULL‑allowed varchar(20) utf8mb4:

key_len = 20*4 + 1 (NULL flag) + 2 = 83

Conclusion

Because NULL columns consume extra storage, complicate index statistics, and introduce logical pitfalls in queries, it is generally better to define columns as NOT NULL with appropriate default values unless a genuine business need exists.