Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

mysql performance null values

Posted on 2009-02-13
4
Medium Priority
?
357 Views
Last Modified: 2012-05-06
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/
0
Comment
Question by:erenpasa
  • 2
4 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 23631693
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
 

Author Comment

by:erenpasa
ID: 23631725
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
 
LVL 26

Accepted Solution

by:
Umesh earned 750 total points
ID: 23631726
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
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 750 total points
ID: 23631758
^^ + 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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month21 days, 3 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question