mysql performance null values

Posted on 2009-02-13
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.
Question by:erenpasa
    LVL 25

    Expert Comment

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


    Author Comment

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

    Accepted Solution

    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.

    LVL 25

    Assisted Solution

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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video discusses moving either the default database or any database to a new volume.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now