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.

When we define a table, multiple times we assign default value of a column as NULL. I have

  • How NULL is different than empty value('') as well as 0?
  • Is there any impact of NULL value in query performance?
  • How it affects the index created on that column?

Thanks...

share|improve this question
add comment

2 Answers

  • NULL is different from empty string '' and 0. Empty string as well as 0, are existing values that can be used in equations and other operations.

'' = '' is true

0 = 0 is true

NULL = NULL is not true

Quoted from Wikipedia:

Null is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database.

  • I'm not sure what kind of impact on performance would be expected with NULLs instead of ''s or 0s, but AFAIK no, there is no big difference in performance between the two. Somebody may correct me if I'm wrong. An exception to this rule is noted below.

  • First of all, with NULL you can have a UNIQUE INDEX on a column, and still have many NULL values. This is by definition of the SQL NULL. Read more at mysql reference

Also, if you use InnoDB (I'm only saying this because your question is tagged with mysql-5.1), then no value is stored at all, which may result in big reduction in storage and possibly faster searching (since more rows fit into one page)

On the contrary, with MyISAM, nullable column indexes may have increased size causing slightly increased storage space required.

In the end, it is neither bad, nor good practice to use NULL columns. Combined with bad design they can be a headache. But with good design they are useful and helpful. NULL columns deserve to have their position in well designed databases, they're just not appropriate for every column.

share|improve this answer
add comment

I don't believe you will be gaining much performance by having nulls, and indices should be about the same. A B-tree will still have to have a nodes pointing to NULL record as it would for empty strings or 0.

As an application developer, you usually want to stay away from NULLs in your database if possible. The reason is then when pulling data out in fields that have not been set and are null, must then be cast into something that can be presented in a UI layer. We have seen performance issue arise from this, where it is quicker to set an appropriate default value in the DB once at the time of insert, and then do no casts the numerous times the record is read. Of course it would depend on the specific needs of your application.

Additionally NULLs will generally kill any kind of computation or concatenation run against them. "Bob" + NULL for example is NULL, where "Bob" + "" is "Bob".

share|improve this answer
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.