Jump to content
Larry Ullman's Book Forums

Mysql: Null Fields


Recommended Posts

From reading in the book and posts in the forum I understand having table fields set as NULL is bad and takes up more resources or processing power than table fields that are not set to NULL.

I don't understand this really, the backend of MySQL and why this would be bad. Can you explain this a little further, I mean how MySQL looks at the types and values in a field and why NULL makes the engine work a little harder OR is that not exactly what is going on when it reaches a field that is set to NULL.

 

So, if I have a field that can in fact have no value set to it then it is better to actually set a value for that field with default or NULL or empty space or something such as a 0 instead of clicking the set to null checkmark in phpMyAdmin when manually creating table fields that way or setting to NULL in SQL syntax?

 

 

Thanks.

Link to comment
Share on other sites

I mean actually setting a value for that field and having it automatically populated with a value of NULL or 0 or even blank empty space? Why does blank empty space a better option than setting NULL to the field itself or is that also a bad idea?

Link to comment
Share on other sites

Hello Terry, thanks for the question. In terms of MySQL, NULL values can hurt performance in that MySQL may not be able to take advantage of indexes on fields with lots of NULL values. Setting an empty value (an empty string or 0) may be slightly better than NULL in terms of performance but most likely is not a better idea in general (especially an empty string, as you may end up doing comparison on that field and an empty string does not equal NULL, although they may look the same to the naked eye).

 

To me, NULL values, especially a lot of them, indicate a poor, non-normalized database design. On the book I'm working on, I just designed two databases, with three tables each, and there's not a single column that allows for NULL values. I'm not saying it should never happen, but columns with NULL values shouldn't be common. Again, the rules of normalization tend to lead to more tables with few or no NULL-possible columns.

 

Let me know if it's still not clear.

  • Upvote 1
Link to comment
Share on other sites

 Share

×
×
  • Create New...