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

MySQL Benchmark – updates by primary vs seco... (Note: when I'm talking about MySQL I usually assume InnoDB storage engine. Any other case I explicitly tell this is MyISAM or Memory etc.) I've he...
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...
Sleeping connections Why is it so important to close connections to databases if there's no explicit need to keep them open (which usually the case)? Symptoms Icinga ...

About charlesnagy

I'm out of many things mostly automation expert, database specialist, system engineer and software architect with passion towards data, searching it, analyze it, learn from it. I learn by experimenting and this blog is a result of these experiments and some other random thought I have time to time.
Bookmark the permalink.