What values are not stored on disk by InnoDB?

Question

I have a pretty big table in InnoDB, and I want to minimize its size, within reason. For a few of the columns, the values are almost always the same in every row, so I want to try to take advantage of this.

For example, if I have an int column (or datetime or varchar or whatever), would a null value in that column not actually be stored on disk? That is, would it be smaller than storing the number 1 in every row?

Or in an int not null default 0 column, would 0 take up less space than 1, or even no space at all?

(I know I can just try it on some big tables, but for my real data it'd be slow to try, and measuring sizes of things in databases can be finicky, so I'd like to hear somebody speak somewhat knowledgeably!)

Answer 1

The storage for each column depends on the datatype requirement.

1 as an INT takes 4 bytes, but 1 as a BIGINT takes 8 bytes.

If performance is not an issue check out compression for InnoDB.

Answer 2

An int is an int is an int, so a 0 will not take up less space than a 1. And a NULL is just going to be a special value that sits in the same space.

It sound like you're asking about a sparse column setup, where NULLS are represented by a value that is significantly smaller than the data type, or are not stored at all. I can't find anything on that for InnoDB, but there's a bunch of stuff out there about sparse columns in SQL Server 2008.