Sunday, April 22, 2018

Advantage of using NULL over Empty Strings or 0.00

NULL means a column is empty or not set. An empty string "" or 0 or 0.00 is not the same NULL.
0 means has a value but NULL is not a value.

It depends on which storage engine you use.

In MyISAM format, each row header contains a bitfield with one bit for each column to encode NULL state. A column that is NULL still takes up space, so NULL's don't reduce storage

In InnoDB, each column has a "field start offset" in the row header, which is one or two bytes per column. The high bit in that field start offset is on if the column is NULL. In that case, the column doesn't need to be stored at all.
So if you have a lot of NULL's your storage should be significantly reduced.

The only way I can imagine NULLs improving performance is that in InnoDB, a page of data may fit more rows if the rows contain NULLs. So your InnoDB buffers may be more effective.

  1. 1 NULL requires 1 byte 
  2. 1 Empty String ' ' requires 1 byte (assuming VARCHAR)
  3. 1 Zero requires 4 bytes (assuming INT) 

You start to see the savings here:

  1. 8 NULLs require 1 byte
  2. 8 Empty Strings require 
  3. 8 bytes 8 Zeros require 32 bytes 

On the other hand, I suggest using NULLs over empty strings or zeros, because they're more organized, portable, and require less space. To improve performance and save space, focus on using the proper data types, indexes, and queries instead of weird tricks.

NB: This suggestion is not for MyISAM. NULL using is not good for an index column.

No comments:

Post a Comment

Total Pageviews