I have a table structure shown below contains Structure of Roles Table i taken:
Let it be a "roles" table contains some records related to roles of users.
Now here i have taken one column "is_archived(int)" which i am using to get to know that role still exists or deleted.
So i am considering two values for that column:
"NULL"=> if that role still exists (like TRUE),
"1" => if deleted /inactive (like FALSE)
For my table maximum records will contain "NULL" value for this column and Default value is also "NULL". Now i am in a dilema that is there any performance issue in this case as i am using "NULL" instead of "0".
I need to know the pros and cons of this case(Like "Search Performance", "Storage", "indexing", etc).
And in case of cons, what are the best alternative ways.
Thanks for your answers.
I agree with @RickJames about NULL. Don't use NULL where you mean to use a real value like
true. Likewise, don't use a real value like 0 or
'' to signify absence of a value.
As for performance impact, you should know that to search for the presence/absence of NULL you would use the predicate
is_archive IS [NOT] NULL.
If you use EXPLAIN on the query, you'll see that that predicate counts as a "range" access type. Whereas searching for a single specific value, e.g.
is_archive = 1 or
is_archive = 0 is a "ref" access type.
That will have performance implications for some queries. For example if you have an index on (is_archived, created_on) and you try to do a query like:
SELECT ... FROM roles WHERE is_archived IS NULL AND created_on = '2017-01-31'
Then the index will only be half-useful. The WHERE clause cannot search the second column in the index.
But if you use real values, then the query like:
SELECT ... FROM roles WHERE is_archived = 0 AND created_on = '2017-01-31'
Will use both columns in the index.
Re your comment about NULL storage:
Yes, in the InnoDB storage engine, internally each row stores a bitfield with 1 bit per column, where the bits indicate whether each column is NULL or not. These bits are stored compactly, i.e. one byte contains up to 8 bits. Following the bitfield is the series of column values. A column that is NULL stores no value. So yes, technically it is true that using a NULL reduces storage.
However, I urge you to simplify your data management and use
false when you mean
false. Do not use NULL for one of your values. I suppose there's an exception if you manage data at a scale where saving one byte per row matters. For example, if you are managing tens of billions of rows.
But at a smaller scale than that, the potential space savings aren't worth the extra complexity you add to your project.
To put it in perspective, InnoDB pages only fill each data page 15/16 full anyway. So the overhead of the InnoDB page format is likely to be greater than the savings you could get from micro-optimizing boolean storage.