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.

  • 1
    You should allow NULL if and only if the NULL value has an interpretation for the thing you are modelling. Mar 21, 2014 at 11:11

2 Answers 2


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.


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

  • 3
    Once again, the best way to know for sure is profiling and testing.
    – jcolebrand
    Jan 5, 2011 at 5:27
  • I would be careful with such broad statements - if you are writing 10 million rows a night into a table via some ETL process and that table has a bunch of Not Null constrained fields, you will see the performance impacts.
    – ScottCher
    Jan 5, 2011 at 15:21
  • 2
    +1: Maybe it's not true for all applications, but for what I'm doing getting consistent/correct data is more important than saving some space or losing some speed.
    – j.p.
    Jan 13, 2011 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.