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?

What storage engine and row format are you using? You can check this by querying information_schema: `select engine, row_format from information_schema.tables where table_schema = 'your_schema' and table_name = 'your_table';` — Ike Walker, Feb 28 '17 at 21:29
@IkeWalker MyISAM, dynamic (I can not affect these settings) — jave.web, Mar 01 '17 at 00:29
Creating new table for storing new described column is best way (as I know!) and JOIN with big table for usage. it was suggested by some one that I had mentioned in my answer. @jave.web — MohaMad, Mar 01 '17 at 11:07

score 0 · Answer 1 · edited May 23 '17 at 12:25

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.

score 0 · Answer 2 · answered Feb 28 '17 at 21:35

(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

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

2 Answers2