Geekalicio.us

Ok, really the question is whether to use NULL or NOT NULL, but NOT to NULL, was just more Shakespearean.  As I update one of my commercial PHP scripts, I’m looking to optimize it as much as possible for the end customers.  So, among the bazillion of performance enhancing script decisions to be made, is whether to leave your mySQL columns as NULL or NOT NULL.

The general rule of thumb is you leave it as NULL for fields in your table that don’t really need to be filled.  For example, if you had a User table, and ‘eye color’ was just an optional field, you could leave that at the default, or NULL value.  If you set that field to NOT NULL you would be forcing that field to have some value, even if the value is empty string.

Now, I could simply set all fields to NOT NULL and default it to an empty string.  Then I would not have to deal with NULLs, and just deal with empty string checks, which some may prefer.  But, what is the performance hit of doing that?  By setting it to NOT NULL you are telling mySQL that you need to make some space for this value, and even an empty string takes up some space.  After much Googling, I’ve pretty much come to the conclusion that the space taken up by the empty string is negligible for most folks, and really isn’t a concern or point of debate in the NULL vs NOT NULL decision.

So, what to do – and here’s the answer, do whatever works best for you in your situation and ignore any minute performance/size issues.  If you want to follow strict database paradigm, use NULL for place where you want to know that no value has been entered.  So, if the user chose to not fill out their eye color in the above example, the resulting mySQL field value would be NULL.

There you have it – clear as mud .

Here’s the mySQL docs on NULL.