Why Avoid NULL Columns in MySQL: Reasons, Pitfalls, and Performance Impact

MySQL allows columns to be defined as NULL, but many developers use NULL without fully understanding its hidden costs. This article examines why NULL is popular, debunks common misconceptions, and presents solid reasons to avoid NULL columns.

Why people use NULL

It seems convenient because INSERT statements do not need to specify a value.

Beginners often accept the default NULL setting when creating tables.

Is the advice "use NOT NULL" a myth?

The MySQL documentation states that each NULL column adds one extra bit (rounded to a full byte) per row to record the NULL flag. For MyISAM tables this extra byte also affects index size and storage layout, confirming that the recommendation has a technical basis.

Concrete reasons to avoid NULL

Every NULL value can be replaced by a meaningful sentinel value, improving code readability and data integrity.

Updating a NULL column to a non‑NULL value often forces a row rewrite, which may cause index fragmentation.

NULL values break negative predicates such as NOT IN or != , leading to empty result sets.

In timestamp columns, NULL can cause unexpected behavior unless explicit_defaults_for_timestamp is enabled.

Aggregations like COUNT(column) ignore NULLs, potentially skewing statistics.

Index length calculation includes an extra byte for the NULL flag, making indexes larger and slower.

Example schema and queries

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

Running a query that uses NOT IN on a column containing NULL returns no rows, illustrating the logical pitfall:

select user_name from table_2 where user_name not in (select user_name from table_3 where id!=1);

Index creation also shows the impact of NULL:

create index IDX_test on table_3(user_name);

When examining the EXPLAIN output, the key_len for a VARCHAR(20) column differs because of character set (utf8 vs utf8mb4) and the NULL flag: 62 bytes for NOT NULL (utf8) versus 83 bytes for NULL (utf8mb4 + flag).

Key takeaways

A NULL column consumes an extra byte per row for the NULL flag.

NULL complicates index statistics, query planning, and can cause index fragmentation.

Prefer NOT NULL with appropriate default values to simplify schema, improve performance, and avoid subtle bugs.

For further reading, see the original article and the referenced MySQL documentation on NULL handling and index length calculation.