1

Sometimes an absent value can be represented (with no loss of function) without resort to a NULL-able column, e.g.:

  • Zero integer in a column that references the AUTO_INCREMENT row ID of another table
  • Invalid date value (0000-00-00)
  • Zero timestamp value
  • Empty string

On the other hand, according to Ted Codd's relational model, NULL is the marker of an absent datum. I always feel better doing something "the correct way" and MySQL supports it and the associated 3-value logic, so why not?

A few years ago I was woking on a performance problem and found that I could resolve it simply by adding NOT NULL to a column definition. The column was indexed but I don't remember other details. I have avoided NULL-able columns when there is an alternative since then.

But it has always bothered me that I don't properly understand the performance effects of allowing NULL in a MySQL table. Can anyone help out?

  • possible duplicate of [NULL in MySQL (Performance & Storage)](http://stackoverflow.com/questions/229179/null-in-mysql-performance-storage), interesting, but asked and answered before – ajreal Jan 12 '11 at 15:16
  • You could start using nullable columns again (where appropriate) and next time you have a performance problem that can be solved by changing a column to NOT NULL, post the exact details. Then you will hopefully get an answer which allows you to understand the issues involved, and also perhaps cure your fear of nullable columns at the same time. – Mark Byers Jan 12 '11 at 15:16
  • @ajreal: unfortunately, while interesting, http://stackoverflow.com/questions/229179/null-in-mysql-performance-storage doesn't answer my question. –  Jan 12 '11 at 15:53
  • @Mark Byers: I know the question could really use an example. I'll see if I can create one. –  Jan 12 '11 at 15:54

1 Answers1

0

It saves 1 bit per column. http://dev.mysql.com/doc/refman/5.0/en/data-size.html Doesn't seem like much, but over millions of rows it starts to make a difference

sreimer
  • 4,913
  • 2
  • 33
  • 43