mysql performance null values

Hi to all,
I wonder that.When i create a table should i use default value for numeric and alpha numeric fields?
If i use allow null option so is there any decrease performance?
Shortly which of the best way for performance, "allow null" or "not allow null"...
Pls tell me your opinion and if you have a text or link for this subject so let share with me pls.

thanks to all

I've read this text. you should read it before your reply.
http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/
erenpasaAsked:
Who is Participating?
 
UmeshConnect With a Mentor MySQL Principle Technical Support EngineerCommented:
You may want to take a look here

http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

You should allow NULL in any column where "I don't know" is a possible answer. Remember, NULL is NOT the same as zero or empty/blank. Zero means nothing or none, which is a definite value.

Personally I have a opinion that all mysql engines are optimized to handle NOT NULL field checking quickly, so there's no real performance gain from whether the column is of NULL or NOT NULL defined.

.
0
 
reb73Commented:
If a field is a candidate key (primary or foreign) or input is mandatory, do not allow nulls.. If necessary use default constraints to default the value to a non-null value..

If a field is optional and is only ever gonna be sparsely populated, allow nulls..

Nulls are good for performance, but any comparison or joins on nullable fields needs to be explicitly handled using a <field> IS NULL or ISNULL() call, which means taking care during writing queries and procedures..

0
 
erenpasaAuthor Commented:
hi reb,
i think "allow null" option is good choose for insert and update performance? but for query performance is not good choose? am i right? so query performance most important for me.
0
 
reb73Connect With a Mentor Commented:
^^ + 1

Query engines in general are well equipped to handle checks for NULL values, but many issues in queries relate to the check for NULLS beind dispensed with which can affect the results.

It is likely that columns declared to allow nulls are usually not popular for indexing, but in general should perform as well in queries when compared to non-nullable columns, if not better..

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.