Comments on: Choosing MySQL boolean data types

By: shlomi

shlomi — Sat, 09 Apr 2016 06:12:14 +0000

@Masoud, please see comment #6

By: Masoud

Masoud — Sat, 09 Apr 2016 02:32:46 +0000

Indexing a column that has just 2 different values, like true/false, is useless, and instead of improving read performance, slows down writes… Your data must be unique (like Primary Keys), or at least have less duplicates (like users first/last names) to use advantages of indexing.

By: Janis

Janis — Sun, 14 Apr 2013 14:51:32 +0000

So it is about to guessing when mysql will have simple boolean data type? It is rather funny to see how all of us make workarounds of design fault or bug in mysql.

By: shlomi

shlomi — Tue, 22 Jun 2010 05:10:43 +0000

@Andrew,
My bad: with InnoDB, you cannot create an index on CHAR(0) columns anyhow. So my comment #2 becomes irrelevant.

By: shlomi

shlomi — Fri, 04 Jun 2010 10:34:18 +0000

@Ronald,
Good point.

By: Ronald Bradford

Ronald Bradford — Thu, 03 Jun 2010 15:14:46 +0000

It’s also relevant to mention that creating a [VAR]CHAR(5) column to store ‘True’ or ‘False’ is not efficient due to extra bytes. I’ve seen this example before, and changing to ENUM(‘True’,’False’) both addresses no code changes, and reduces the size to 1 byte.

By: shlomi

shlomi — Thu, 03 Jun 2010 12:54:26 +0000

@Pavel: great!

By: Pavel Francírek

Pavel Francírek — Thu, 03 Jun 2010 12:40:47 +0000

You can use (and we mostly do) type ENUM(”,’Y’) so ” translates to False.

By: shlomi

shlomi — Thu, 03 Jun 2010 09:57:10 +0000

@Henrik,

Yes, in case of multiple columns and uneven distribution. Actually, I find this kind of indexing pretty popular. e.g. key(is_unhandled, customer_id).
When distribution is really uneven, even a single-column index on the boolean type can be useful (although my experience shows that you can usually extend to add other columns and benefit more queries using the index).

By: Henrik Ingo

Henrik Ingo — Thu, 03 Jun 2010 09:44:37 +0000

Hmm… So what is a good use case where you even want to index a boolean column? Ok, so as part of a multi-column index it makes sense.

Depending on the app, packing several T/F values into one BIT field may even be an advantage, if there is a case where you use *all* of the boolean values to select rows.