5

In my INNODB mySQL database I have some columns named like active, verified, disabled among others like name, surname.

To explain it better

Column      Type         Null  Default
expires     int(10)      Yes   NULL
verified    tinyint(1)   Yes   NULL
disabled    tinyint(1)   Yes   NULL

When a user logins into my page, I use PHP and check for example

if ($row['disabled']) { }

to know if he is disabled ( NULL or 1 ). (there are only these 2 possibilities).

For now, I set them as NULL but I thought if it is better to use 0 instead, knowing that 0 is empty in PHP too.

Concluding, my questions are two.

  1. Does thousands NULL records grow without a reason the DB instead of thousands 0 records?
  2. Does NULL or 0 affects PHP execution's performance differently ? If so, what is the best combination of mySQL and PHP to have for the requested checks as above?

Update

On my question number 1, my question is if NULL is actually null of size, otherwise is +3 bytes right?

EnexoOnoma
  • 8,454
  • 18
  • 94
  • 179

3 Answers3

4

Does thousands NULL records grow without a reason the DB instead of thousands 0 records?

Yes, allowing NULL in a column grows the size of the database, after that though storing NULL's does not take up extra space. If a column is defined as NOT NULL, it takes up less space.
An exception is varchar(), if you have varchar() column that allows NULL, and many rows are NULL, it might take up less space.

Should I change the way I make the PHP checks for better perfomance ?

I doubt it will matter much, you should not the checking 1000's of records in PHP anyways.
You should be doing: 

SELECT * FROM  a WHERE disabled = '1'

Or

SELECT COUNT(*) FROM a WHERE disabled IS NULL

And besides, you can always do

SELECT IFNULL(disabled,0) as disabled FROM ....

Don't try to do the DB's job in PHP.

One word of warning.....

If a row has two values, putting an index on it will likely not help, unless one of the two values is rare.
This is called low cardinality of a database column.
For more info see here: Does it make sense to use an index that will have a low cardinality?

If one of the two values is rare, make sure you test for that value, not the other way round.

To recap
Define your table as: 

Column      Type         Null?  Default  Comment
id          integer      No     Auto_inc  Primary Key 
expires     datetime     No     NULL      Must be supplied
verified    boolean      No     0         boolean = tinyint(1)  
disabled    boolean      No     1         -- but the intend is clearer.

You can even use a BEFORE INSERT TRIGGER to have MySQL automagically set the expires field to NOW() + 30 days.

Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319
  • Hi, I updated my question with the columns I use with their types. My mistake, I didn't write that those checks are made when a user logs into my page so it is not a db's job. But I will use those queries for sure into the admin page. I didn't understand well your first sentence **after that though storing NULL's does not take up extra space.**. I think that your opinion is to go with **0** instead of **NULL** right ? – EnexoOnoma Sep 03 '11 at 03:41
  • 1
    My point is that the waste of space happens when the tablelayout is defined, not when you actually fill a field with `null`. – Johan Sep 03 '11 at 03:49
4

In mysql, NULL values mean that a particular field has no value in it or it is empty. Ideally, if you have a field like Name and Address and don't want to assign values to them all the time, you can state that it's default value is NULL.

But if you're talking about only two possibilities like a True or False item, it's best to go for the Boolean field that is most space efficient.

Going further, for fields like Booleans (eg: disabled) and dates (eg: DateJoined) it's not advisable to keep them as null or unassigned. You can bump into problems in the future if you had to interface with another language like VB. For example, if you try to read an assigned date field via VB, you'll see that your app crashes. But through PHP, this is not a problem. To be on the safe side, it's best to assign a default true or false (1 or 0) value for such fields.

And finally, to CHECK for null values, the normal relational operators are not suitable. Rather, you should use the IS NULL or IS NOT NULL operator instead.

More info here: http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

itsols
  • 5,406
  • 7
  • 51
  • 95
  • Thank you for it, so I guess I will use boolean not null. – EnexoOnoma Sep 03 '11 at 03:52
  • Yes, and let me clarify. I think you're still unsure. NULL is not a data type but Boolean is. NULL is what it contains. So you should store either True or false (ie., 1 or 0) and not keep it as null. This is the best approach. – itsols Sep 03 '11 at 04:02
  • Yes, I meant Boolen for type and not set it as null. Thanks again! – EnexoOnoma Sep 03 '11 at 04:04
1

This question has absolutely nothing to do with performance and data size.
Default value should be chosen by sense, not quantity.
It is as if you asked, what fuel will be be better for your car - gasoline or uranium. The fuel intended to use with your car!

NULL and 0 are different values, with different meaning.

  • NULL means there is NO value. And will affect some queries, where clause for example.
  • 0 means perfectly valid value, equal to 0

So, you have to choose one suits your logic, not some groundless fears of consuming enormous amount of space - whole 1 megabyte per 1000000 users.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345