We help IT Professionals succeed at work.

mysql performance null values

erenpasa asked
Medium Priority
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.
Watch Question

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..


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.
Senior Principal Technical Support Engineer
Top Expert 2009
You may want to take a look here


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.


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
^^ + 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..

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.