Why Avoid NULL Columns in MySQL: Performance and Indexing Considerations

NULL values are a common source of bugs in programming, and in MySQL they bring additional storage overhead and complexity.

According to the MySQL documentation, each NULL column adds a bit (rounded to a full byte) per row to record whether the value is NULL.

Using NULL columns can increase row size, make index statistics more complicated, and degrade query performance, especially when the column participates in indexes.

Examples show that a NOT NULL varchar(20) column results in a smaller index length than a nullable column because the nullable column requires an extra flag byte and may use a different character set.

The calculation of key_len is explained: for utf8mb4, key_len = 20*4 + 1 (null flag) + 2 (varchar length bytes) , while for utf8 it is 20*3 + 2 .

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 returns empty when NULL is present
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;

-- CONCAT with NULL yields NULL
select CONCAT("1", null) from dual;

Additional observations include that NULL columns cannot be updated in‑place, may cause index splits, and that comparisons using = NULL are always false; the correct check is IS NULL .

In conclusion, it is advisable to avoid NULL columns when possible, define NOT NULL constraints with sensible default values, and be aware of the storage and performance implications of nullable fields.