Why Setting MySQL Columns to NOT NULL Boosts Performance and Saves Space

Query Optimization

When a column is declared NOT NULL, MySQL knows the column never contains NULL values, allowing the optimizer to skip NULL‑related checks and accelerate queries, especially on large tables.

Reduced Storage Space

InnoDB stores rows using the COMPACT format. A separate "NULL value list" records which columns are NULL, using one bit per column. If every column is NOT NULL, this list disappears, saving one to several bytes per row, which becomes significant at scale.

Index Efficiency

Columns that can be NULL make B‑tree index optimization harder because NULL is a special value that does not compare equal to anything. Queries involving NULL often require extra logic, and the optimizer may choose less efficient plans. col = 5 – uses the index normally. col IS NULL – can use the index but with more complex handling. col != 5 (or col <> 5) – if the column contains NULL, the result can be unpredictable and the optimizer may abandon the index.

Consequently, a table that allows NULL can be an order of magnitude slower under high concurrency compared to a NOT NULL design.

Avoiding Aggregation Distortion

When a column like coupon_no is NOT NULL, a simple count query works:

SELECT COUNT(*)
FROM orders
WHERE coupon_no != '';

If the column permits NULL, the query must filter out NULLs explicitly:

SELECT COUNT(*)
FROM orders
WHERE coupon_no IS NOT NULL
  AND coupon_no != '';

Aggregates such as AVG automatically ignore NULL values, which can inflate the average if missing values represent zero. Functions like MAX, MIN, AVG, and SUM ignore NULL, while COUNT treats NULL specially and may require COUNT(column) or COUNT(*) with explicit filters.

For more details on COUNT behavior, see the article "MySQL中COUNT(*)、COUNT(1)和COUNT(column)，到底用哪个？".

Simplifying Application Code

Declaring a column NOT NULL removes the need for repetitive null checks in code. For example, Java code that checks user.getAge() == null can be replaced with a simple numeric default (e.g., 0) when the column is NOT NULL:

if (user.getAge() == null) {
    // do something
} else if (user.getAge() == 0) {
    // do something else
}

Using NOT NULL with a default value (e.g., 0 for unknown age) simplifies logic and improves readability.

Improving Data Consistency

The NOT NULL constraint enforces that every row contains a value for the column, preventing incomplete or inconsistent data. For instance, a username column set to NOT NULL guarantees that every user record has a valid name.

When NULL Is Acceptable

Not all fields should be forced to NOT NULL. Optional attributes such as a middle name, a deletion timestamp ( deleted_at), or free‑form remarks can legitimately be NULL to represent the absence of a value.

Conclusion

Overall, using NOT NULL for MySQL columns offers performance gains, storage savings, clearer code, and stronger data integrity. The benefits generally outweigh the drawbacks, and the best practice is to define columns as NOT NULL unless the business domain explicitly requires nullable values.