Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
You should allow NULL if and only if the NULL value has an interpretation for the thing you are modelling. –  jameshfisher Mar 21 at 11:11
add comment

2 Answers

up vote 18 down vote accepted

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.

share|improve this answer
7  
This is not necessarily true in PostgreSQL. Null columns save space, which can improve speed, and processing time should be about the same. –  Peter Eisentraut Jan 4 '11 at 5:04
3  
This is also not true for Oracle. In addition, unlike MySql, Oracle does not index nulls so you can reduce the size of your indexes using them. See stackoverflow.com/questions/289001/does-mysql-index-null-values –  Leigh Riffel Jan 5 '11 at 21:45
add comment

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

share|improve this answer
3  
Once again, the best way to know for sure is profiling and testing. –  jcolebrand Jan 5 '11 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 '11 at 15:21
1  
+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 '11 at 9:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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