Disk space implications of setting MySQL column value to NULL instead of 0 or ''

Question

I'm trying to understand the best way to handle columns which are mostly empty in terms of disk-space and index-performance. Is there a difference between putting in all empty places NULL vs '' (for varchar / text) vs 0 (for int).

Thanks.

Answer 1

No, using NULL will not take up less space than an empty VARCHAR or INT field. In fact it may take up more space. Here's why:

A VARCHAR is stored as a size + value. The number of bytes used for the size depends on the max storage of the VARCHAR. VARCHAR(255) requires one byte, VARCHAR(65536) requires two bytes and so on.

So that VARCHAR(255) column takes up one byte even if you store an empty string. The following table would take a minimum of one byte per row (plus some other possible overhead depending on storage engine).

CREATE TABLE sample (
  a VARCHAR(255) NOT NULL
);

To save NULL values, MySQL uses a bitmask for each row. Up to 8 nullable columns can be stored per byte. So if you have a table like this:

CREATE TABLE sample (
  a VARCHAR(255) NULL
);

Would take a minimum of two bytes per row. Storing NULL just sets the bit, it's already reserved whether you use it or not. The byte for the VARCHAR's size is still used for each row even if the column is set to NULL.