Does setting column property to NOT NULL affect table size?

Question

I have a bunch of columns in my MySQL database table that store booleans (0 or 1) as tinyints. They are 0 by default and are not meant to be null. I am thinking about setting the column property to NOT NULL so that there is another validation check just in case. I wonder though whether it will come at a cost of increased size of the table down the road. My fear is that, perhaps, NOT NULL property adds another bit to every data entry?

My database is new and practically empty so when I try to check the size of the table it always gives me 16kb.

Answer 1

From the MySQL documentation (emphasis mine):

Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.

So, on the contrary, NOT NULL saves disk space, rather than costing more.

Answer 2

Which Engine are you using?

At most, 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?

See also 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 NULL versus 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 NULL:

• Not yet set (eg, date of death)
• Optional (eg, middle initial)
• Not available (eg, middle initial)
• etc.

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 UNSIGNED.
• Get the CHARACTER SET and COLLATION correct.
• NULL vs NOT NULL. (For logic reasons, not speed or space)
• etc.