Which Engine are you using?
NULLs take 1 byte per 8 nullable columns. In the other direction, a
NULL value may occupy less space. What datatype are you talking about?
SET. This datatype allows you to put up to 64 boolean flags in up to 8 bytes of space.
Long ago, I decided that the space considerations of
NOT NULL were so insignificant as to be not worth the time to think about it. I have a Rule of Thumb: If a tentative optimization (
NULL, etc) is not going to save 10% of something (space, speed, etc), then drop the topic and look for something else to optimize.
Instead, I focus on using
NOT NULL except when I have a business reason or a processing logic that can make good use of
- Not yet set (eg, date of death)
- Optional (eg, middle initial)
- Not available (eg, middle initial)
I do insist on thinking carefully about datatypes when initially creating a table. It is painful to make schema changes later. Examples:
- Will this fit into a 2-byte
SMALLINT instead of a 4-byte
INT. (Smaller saves disk space and helps speed)
- Virtually all ints can/should be
- Get the
CHARACTER SET and
NOT NULL. (For logic reasons, not speed or space)