Why should we keep column as NOT NULL instead of NULL in MySQL?

Keep table column as NOT NULL instead of NULL except in some special cases. This statement is cited by many articles of MySQL optimization, but they don't say why we should do this. Here we discuss about this.

First why are there many people using NULL when defining table? The reasons may be:

1. NULL the the default when defining table, the rookies or people who don't want to have much troubles will keep this default setting
2. Many people think NOT NULL will require more space
3. Many people don't want to verify the data before inserting or updating, it's more convenient when writing SQL queries.

There are many resources on the Internet saying:

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.” -- MySQL official website document

It's difficult for MySQL to optimize NULL column, it will make index, index statistic and value more complex. NULL column needs more space, it also requires MySQL do some special operations internally. After indexing, NULL column will contain one more byte.

But the performance improvement of changing NULL to NOT NULL is not so obvious. Unless you can ensure that it brings performance bottleneck, otherwise don't make it as an optimization option. The most important thing is to determine the column data type carefully.

Reference : http://www.phpben.com/tb.php?sc=8a06cf&id=71