1

When we need to store boolean fields in the database, which is better? true and null or true and false combination.

I have seen many article which says to use true and false. Some reports say true and null combinations provide better performance.

I am designing a website with Ruby on Rails and mariadb. I need to add a boolean status field to a table with 1 million rows.

The purpose of this status field is for filtering and reports.

2
  • Hi, and welcome to dba.se! Will there ever be NULLs in the fields or not?
    – Vérace
    Aug 22 at 8:21
  • If I am going to use true , false combination I would not allow null in column. If I need to use true , null instead of false, then there is going to be null instead of false. Which combination is better ?
    – Ajit Hegde
    Aug 22 at 8:38

2 Answers 2

2

In MySQL / MariaDB / InnoDB, NULL is indexed as if it were just another possible value.

What does matter...

  • Use NULL for business logic, not performance. For example: 'not yet answered', 'decline to state', 'N/A', 'not yet known' (eg end_time), 'do not care'.

  • It is better to test for equality: (x = 0, x = false, x IS NULL, x = 1, x = true) than for inequality. (Some, not all, optimizations work better with equality tests.)

  • As a rule, a single-column INDEX on a boolean quantity will never never be used. The exception is when one of the values occurs very infrequently and you are testing for equal that value.

  • On the other hand, it is quite fine to have a composite INDEX containing boolean column. (Again, you need to test for equality, not inequality.)

  • Even if you put the boolean first and it has poor selectivity, the Optimizer may be able to make very good use of the index. In this extreme example, INDEX(is_deleted, created_at) makes it so that only 1 row needs to be looked at, regardless of table size or selectivity:

    WHERE is_deleted = false  -- Similarly:  deleted_date IS NULL
    ORDER BY created_at       -- ASC or DESC
    LIMIT 1
    
0

Which is better?

True and False
True and Don't Know.

NULL != True
NULL != False 

Also ...
NULL values are frequently not included in Indexes, so any kind of searching based on WHERE .. IS NULL is likely to run poorly.

I need to add a boolean status field to a table with 1 million rows.

On it's own, a field that only ever has two values is a very poor discriminator and so an Index on this field alone may well be discounted by the Query Optimiser. Instead, add this field onto your existing Indexes, to "qualify" your other ways of filtering the data.

1
  • 2
    The question is tagged mysql and mariadb, which both use InnoDB as their storage engine. InnoDB does include NULLs in indexes. WHERE ... IS NULL can use the index. See dev.mysql.com/doc/refman/en/is-null-optimization.html Though as you say, if it's not selective, the optimizer may skip using that index. Aug 22 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.