1

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.

5

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.

1

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.
  • Thank you for a very informative answer. I am using default InnoDB, utf8, utf8_unicode_ci collation. I suspect I will have to change it later, but since the application is still in development, I kick it down the road. I was looking to adapt a consistent convention regarding NULLs and NOT NULLs so I don't have to think much every time I create a column that can be one of those. I guess always using NOT NULLs for columns that cannot be NULL is a good convention. – Arthur Tarasov Aug 22 '17 at 7:51
  • 1
    Some queries can perform better if the Optimizer knows that a column can never be NULL. (This is rather rare.) – Rick James Aug 22 '17 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.