NULL or NOT NULL by default?

Question

In MySQL, is it better to always allow nulls unless you know a field is required, or always use Not Null unless you know a field will contain nulls? Or doesn't it matter?

I know in some DBMSs they say to use Not Null as much as possible because allowing nulls requires an extra bit (or byte?) per record to store the Null status.

Answer 1

In most DBs a NOT NULL column will be more efficient in terms of stored data for the reason you state, and also more efficient to query and index - so unless you want to allow NULLs in a column you should explicitly disallow them.

There will be a slight performance implication, as the extra NOT NULL constraints will potentially need to be checked for each row you affect with any INSERT or UPDATE but as most databases are relative write-light and read-heavy this is probably not a concern (the little extra time taken is unlikely to be noticeable at all anyway as it is a CPU-bound operation where the rest of the insert/update operation will be IO-bound and so a much more significant bottle-neck) and it gives you some "free" data checking so your code (or other people's code) can not accidentally put NULLs where other code is not expecting them and so may give incorrect results in their presence.

Edit: As Peter points out in his comment the above is a generalism and may not hold true for all DMBSs, though I'm pretty sure it does for mysql and mssql. Other complications in the area might include features like sparse tables (as implemented MSSQL 2008 for instance) which will alter the performance dynamics of (not)nullable columns.

Answer 2

You should let your schema design and application requirements guide this decision. The performance differences are probably not noticeable either way in most cases.