0

I have a table with quite a lot entries. I need an additional column with an integer value or null. The thing is that only very few rows will have that field populated.

So i wonder whether its better to create a seperate table where i link the entries in an 1:1 relation.

I know one integer entry takes 4 bytes in mysql/myisam. If I have the column set to allow null values, and only 100 of 100 000 rows have the field populated, will the rest still consume 4 bytes for every null value?

Or is mysql intelligent enough to set the value where it is populated and just regard everything as null, where nothing is set?

The Surrican
  • 29,118
  • 24
  • 122
  • 168

3 Answers3

1

This depends on the ROW_FORMAT value you give when you create your table.

Before version 5.0.3, the default format is set to "REDUNDANT" : any fixed-length field will use the same space, even if it's value is NULL.

Starting with version 5.0.3, the value is set to "COMPACT" : NULL values will never use any space in your database.

You can do an ALTER TABLE to be sure to use the correct format :

ALTER TABLE ... ROW_FORMAT=COMPACT

More details here : http://dev.mysql.com/doc/refman/5.1/en/data-size.html

Dalmas
  • 26,409
  • 9
  • 67
  • 80
  • How could it be possible to use zero space to store null values? It has to cost something are it is not possible to remember the values. – nate c Dec 20 '10 at 17:38
  • @nate c ... well just store the data and every data that has no corresponding part is implicit null – The Surrican Dec 20 '10 at 18:14
  • There is nothing in that link that tells how nulls are stored. If I store 6 columns of ints where ther values are 33,44,66 Three columns are implicitly null. Which 3 columns are they? Thats zero storage. At the least you need a bitfield to tell which columns are there and which are not. – nate c Dec 20 '10 at 18:46
0

As far as my understanding goes, once you declare a field as int, 4 bytes will be set aside for it. So, for 100,000 rows you are looking at ~ 400 KB of space.

If space is a constraint, then separate table will be better. On the other hand, if performance is a criteria, then you'll have to take into account how many times that field is queried and whether it is checked for existence or non-existence. In either case, you'll need a join. If you want to check whether the field is set you can use inner join, which will be slower than single table query. If you want to check for non-existence, you'll need left/right outer join which will be slower than inner join.

Srisa
  • 967
  • 1
  • 7
  • 17
0

It will use bitfields to store nulls so it may need less than one byte. But, even if it did - who cares, unless you are using 3.5" floppies to store your backend in ;-)

NULL in MySQL (Performance & Storage)

Community
  • 1
  • 1
nate c
  • 8,802
  • 2
  • 27
  • 28