24

Does making a datum NULL save any memory versus its normal size?

nerfist
  • 299
  • 1
  • 2
  • 4

3 Answers3

12

This is discussed in the MySQL manual chapter on Storage Requirements. There is no simple answer; it depends on the data type of the column, whether the column is indexed; and the storage engine. The impact of using NULL for a column can vary from nothing to several bytes (depending on how many other columns are also NULL-able.)

The storage impact of declaring a column as accepting NULL, and of actually storing a NULL value, is probably minor. Having a column with lots of NULL values often indicates a need for (further) normalization.

The basic rule is, design your schema based on the properties of the data, not on the storage impact. Fix things only if they turn out to be a problem.

Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
  • _"Fix things only if they turn out to be a problem."_ - Prevention is (maybe sometimes?) better than cure. (no disrespect meant!) – Jay Dadhania Aug 24 '18 at 01:50
  • 8
    @Jay Dadhania - Indeed, sometimes it is. But if we're tossing out aphorisms, I'll offer this: If it ain't broke, don't fix it. In any event, I was merely suggesting a priority: get the data model right, then worry about performance. As a wise person once told me, "I'd rather optimize debugged code than debug optimized code." – Ted Hopp Aug 24 '18 at 05:29
7

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.

If you use a blob field, MySQL will store it in a different file anyway.

Alternatively (my recommendation)
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.

Johan
  • 74,508
  • 24
  • 191
  • 319
  • "_In InnoDB null values take up less space_" - if the table uses REDUNDANT row format, this is only true for variable-length columns. – Ted Hopp Jun 10 '11 at 21:47
  • am I correct to say that value is not stored directly but by reference from row data?. because I have a table partially consisting 2 fields with different data type, but a row can only have value in one of the fields, and null for the other. at begin I thought null value take same space. if I'm correct I feel much better – stackunderflow Jan 06 '20 at 19:04
0

In addition, while a NULL itself does not require any storage space, NDBCLUSTER reserves 4 bytes per row if the table definition contains any columns defined as NULL, up to 32 NULL columns. (If an NDB Cluster table is defined with more than 32 NULL columns up to 64 NULL columns, then 8 bytes per row is reserved.)

sudheer
  • 75
  • 1
  • 5