How to store boolean in MySQL

I always hear newer and newer exotic way to store different types of data in MySQL. People are trying to solve the problem of storing complex and not necessary strictly structured data in databases which is fine. But I never understood why to try to be tricky with the simplest datatypes. For example Booleans. You would believe it’s easy. Yes or no. But there are several different way to say in MySQL that this is a Boolean.

Possible solutions

1) The most common (consider as a best practice):

2) The trivial Boolean or Bool column type which is just a synonyms for TINYINT(1):

3) Declaring an enumaration with “true” and “false”:

4) The newest and weirdest I heard about:

All of them are quite straightforward except the last one which might need some explanation. Char(0) means a zero length string where null value or lack of the value means false and empty string (”) means true. Why would you do so? Because somebody thinks char(0) has optimal for storage requirements. We will see because I decided to test these presumptions.

Environment

The server which hosted the test environment was a KVM box.

8x QEMU Virtual CPU version 0.9.1 3065MHz
4GB memory
High HDD performance

The test was performed with MySQL-server-5.5.16.

Schema

I created a table for each type with the t_xxx_boolean naming convention where xxx is one of the followings: bool, tinyint, enum and char. For collecting results I created a query_times table where I stored query times with 1/10000 sec precision.(You can check out the attached SQL file)

I’ve put index on all boolean column because I wanted the consider it as flag (published, active etc. like everyday usage of bool in models). Also I’ve added a varchar(255) column which is pretty common as well but for the sake of simplicity I always inserted md5_hex (32 characters) to that column . But one thing have to be mentioned:

You cannot create index on CHAR(0)!

Results

The queries was the same all time. Randomly query for true or false values in the tables.

Almost 300 000 query from all type of queries.

Where qtype is:

0: Char column where false
1: Char column where true
2: Tinyint column where false
3: Tinyint column where true
4: Boolean column where false
5: Boolean column where true
6: Enum column where false
7: Enum column where true

Storage

No surprise. All the tables have the same average row length:

Storage wise no difference.

Summary

Don’t try to be too smart. Sometimes the simplest and obvious answer is the right answer. (http://en.wikipedia.org/wiki/Occam’s_razor) Use TINYINT unsigned NOT NULL DEFAULT 0 (or 1).

You might like these too

Unindexed queries can be really expensive The story happened with a webshop application running on Amazon EC2 microinstances. Actually on two instance. Amazon business model is basically simpl...
How to troubleshoot MySQL replication issues? In MySQL a big portion of the problems you're facing is different replication delays so there's no surprise this is one of the most common interview q...
How to decrease IOPS when running MySQL on ZFS Recordsize on data volume This is the first thing you read when it comes to running MySQL on ZFS. InnoDB is using 16k pages so in theory this makes a...