Why You Should Avoid NULL Columns in MySQL: Performance, Indexing, and Storage Insights

When I joined a new company I noticed many columns were not defined as NOT NULL, which bothered me as a perfectionist, so I wrote this article.

In most projects we set all columns to NOT NULL and provide default values. Typical defaults are:

Integers: 0 Strings: empty string '' Dates: 1970-01-01 08:00:01 or 0000-00-00 00:00:00 (with zeroDateTimeBehavior=convertToNull recommended not to use).

Why enforce NOT NULL? A high‑performance MySQL guide states that NULL columns increase index complexity, storage space, and optimizer difficulty. Although the performance gain of converting to NOT NULL is small, indexes on nullable columns can be problematic, especially for InnoDB.

Avoid NULL whenever possible. Most tables contain columns that can be NULL even if the application never stores NULL, because NULL is the default column attribute. Unless you really need to store unknown values, specify columns as NOT NULL. NULL columns make indexing, statistics, and value comparison more complex. They also consume an extra byte per row for the NULL‑bitmap. Exceptions exist: InnoDB stores NULL in a separate bit, giving good space efficiency for sparse data, but this does not apply to MyISAM.

Key points:

If a column is NOT NULL, MySQL will not use NULL as the default value.

NULL complicates indexes, statistics, and value calculations.

Nullable indexed columns increase storage overhead.

Sparse data (many NULLs, few non‑NULLs) benefits from InnoDB's bit storage.

Default Values

In MySQL, a column without NOT NULL defaults to NULL on insert. NULL means the value is unknown, while an empty string or zero indicates a known empty value.

Using MyBatis, insertSelective or manually written insert statements avoid errors when new NOT NULL columns are added.

Value Computation

Aggregates ignore NULL, so COUNT(name) counts only non‑NULL rows, while COUNT(*) counts all rows. Equality checks must use IS NULL instead of =. Any arithmetic with NULL yields NULL, and CONCAT with NULL also returns NULL.

Distinct, GROUP BY, ORDER BY

NULL values are considered equal for DISTINCT and GROUP BY. In ascending ORDER BY, NULL appears first.

Index Impact

Testing shows that adding indexes to nullable columns works, but as the proportion of NULLs grows, index effectiveness degrades, confirming the optimizer’s extra work.

Storage Space

InnoDB rows are stored in four formats: REDUNDANT, COMPACT, DYNAMIC, COMPRESSED. The default is COMPACT. Each row contains a NULL‑bitmap (one bit per nullable column) plus the actual column values. Even though NULL itself occupies no data, the bitmap adds one extra byte per row.

Example row layouts and hex representations illustrate how NOT NULL versus nullable columns affect the stored format.

References: https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html https://dev.mysql.com/doc/refman/5.6/en/is-null-optimization.html https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html https://www.cnblogs.com/zhoujinyi/articles/2726462.html