I cant seem to find a pleasing answer for this on the web for this specfic question, "In MySQL do colums that have 'NULL' waste memory"?

Question

Taken from one of my previous questions.

Table is telling on which item (post , photo or reply) the comment is taking place on.

commentid ---- postid ----- photoid-----replyid
-----------------------------------------------
1                22          NULL        NULL
2                NULL         56         NULL
3                23          NULL        NULL
4                NULL        NULL        55
5                26          NULL        NULL
...
...
...

Is this bad?

Answer 1

It does. I don't know how much and if it's really that relevant but it's never good to have too many NULL-entries.

Answer 2

A better structure would be...

commentid  itemid  itemtype
---------------------------
1          22      post
2          56      photo
3          23      post
4          55      reply
5          26      post

You can even add an index that combines the itemid and itemtype columns so that it's fast to look up all of the comments for a particular item.

Answer 3

It depends on the storage engine, and it's entirely up to said storage engine.

In MyISAM for example, whether a value is NULL or not neither takes up more space nor saves space.

In InnoDB, if a column is NULL its value does not need to be stored at all, thus it saves space. The amount of space saved is the size the value would have occupied had it been there.

More info is in this answer.

The bottom line, however, is that neither will have an appreciable effect on performance. It's a micro-optimization, and it's better to make a decision based on what makes sense in your database design rather than what takes up less memory or runs faster.

Answer 4

Depends what yoiu mean by waste. Making a calumn NULLable means that a Null Inidicator of some sort needs to be stored, I am not sure about MySql but in other DBMSes I know its a smallint with where '1' inidicates null and '0' inidcates the presence of a value.

This extra two bytes gets carried around with the column value wherever it goes. So you will have a two bytes per nullable column stored, in the buffer, and in the structure presented to you program, plus in any index which references the column. So in your example above 20 bytes more disk storage and perhaps 60 bytes of memory has been "wasted". If this worries you then you should probably be coding everything in assembler and using raw disk IO.