Importance of NULL in MYSQL

At the time of creation of any table, one should take care of defining the fields like datatype, datasize etc.
Apart from these we should also take care of whether the field value can be NULL (no value) or not.

What is NULL in mysql ? -

The NULL value is special; because it means “no value”. It is basically a condition, to check whether particular field value is null or not.

If we are not defining the column NOTNULL than MYSQL make it NULL by-default. But We shouldn’t define fields as NULL for any field due to some following reasons -

• If you make any field NULL, it requires more storage space and requires special processing inside MYSQL.

• A nullable column requires extra byte per entry on the time of indexing a column.

• Its harder for MYSQL to optimize queries that refer to nullable column because nullable columns make indexes   and comparisions to value becomes more complicated inside MYSQL.

So, We should define fields as NOT NULL whenever We can.

For Eg. We need to make a table of Oscar awarded singers with columns: firstname , lastname, city, birthdate, death:
All singers will have firstname, lastname, city and birthdate but there can be lot of singers who has been expired.
So, we can put only deathdate column NULL.

CREATE TABLE singers
(
lastname VARCHAR(15) NOT NULL,
firstname VARCHAR(15) NOT NULL,
city VARCHAR(20) NOT NULL,
birthdate  DATE NOT NULL,
deathdate DATE NULL
)

So never forget to define NOTNULL  to any field on which any value must exist….

Hope it will help for your MYSQL optimization….

---

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically each day to your feed reader.

Inside the Web | Powered by WordPress | ElegantBlue is a custom theme by Nathan Rice