MySQL add column without adding any physical data / increasing storage usage?

Question

I have this large table (hundreds of thousands records) to which a column must be added. The column only stores boolean (tinyint 0/1).

The goal is to avoid extra storage usage for already existing records, because they will have NULL/0 by default and 1 can be set in future, but not for all and certainly not all at one time.

So does NULL take some storage space? Or will adding a NULL-able column add any physical data to existing rows?

Answer 1

Johan Answered to this Question about "MySQL: How much space does a NULL field use?"

If you set a field as not null it will take less space in MyISAM.

Setting it to accept null will make it take more space in MyISAM.

In InnoDB null values take up less space, so it might work there.

in his answer, offering to create new table instead of adding new column for your big table:

Another option is to not add the field to this table but to do

table extra_data
  id integer primary key
  big_table_id integer
  large_data_seldom_used varchar(65000)

If you need to select the extra data do:

SELECT large_data_seldom_used FROM bigtable b
INNER JOIN extra_data e ON (e.big_table_id = b.id)

This way you don't have to add an extra field to bigtable at all, saving lots of space if the the extra_field seldom used.

Answer 2

(I'm assuming you are using the InnoDB storage engine and the COMPACT row format)

In theory:

• The NULL values will each take 1 bit of storage in the NULL bit vector in the record header
• The non-NULL values will each take 1 byte of storage

In practice:

• Adding the new column will cause the table to be rebuilt, which in most cases will cause the data to be defragmented such that it takes up less disk space than before, but YMMV