0

I have an existing table in our database which is already populated. I now have additional data I want to store in this table but this data will need to store one additional field (probably a VARCHAR(40) ). Last thing I want is to somehow allocate this space for all the existing entries that won't use it. Is MySQL somehow 'smart' enough not to increase the size of the table significantly if this field is set to NULL or would I perhaps be better off creating a new table for data that includes this extra data field?

Lieuwe
  • 1,734
  • 2
  • 27
  • 41

1 Answers1

1

It depends on which storage engine you are using.InnoDB storage engine (which is default) improves the performance if you have most of the value in your column as NULL.

Because In InnoDB, each column has a "field start offset" in the row header, which is one or two bytes per column. The high bit in that field start offset is on if the column is NULL. In that case, the column doesn't need to be stored at all. So if you have a lot of NULL's your storage should be significantly reduced.

Reference:
SO Answer
Example for null value performance efficiency

Note: If you want to implement the same in MS SQL you may consider SPARSE

Community
  • 1
  • 1
ManirajSS
  • 2,295
  • 5
  • 26
  • 50