MySQL create field non-empty NOT NULL benefits

See: http://blog.yemou.net/article/query/info/tytfjhfascvhzxcyt338

Many tables contain nullable (NULL) columns, even if the application well does not need to save NULL, because nullable is the default property of the column. It is generally preferable to specify column NOT NULL unless you really need to store a null value.

If the query contains nullable columns, it is more difficult for MySQL to optimize because nullable columns make indexing, index statistics, and value comparisons more complex. Nullable columns use more storage space, and special handling is required in MySQL. When a nullable column is indexed, each index record requires an extra byte, and in MyISAM it can even cause a fixed-size index (for example, an index of only one integer column) to become a variable-size index.

It is generally not necessary to change a nullable column to not NULL for a small performance boost, so there is no need to first modify this situation in the existing schema, unless you are sure that this will cause problems. However, if you plan to build indexes on columns, you should try to avoid designing nullable columns. Of course there are exceptions, for example, it is worth mentioning that InnoDB uses a separate bit to store NULL values, so there is good space efficiency for sparse data. But this does not apply to MyISAM.

MySQL create field non-empty NOT NULL benefits