Efficient Boolean value storage for Innodb Tables

Sometimes you have the task of storing multiple of boolean values (yes/now or something similar) in the table and if you get many columns and many rows you may want to store them as efficient way as possible.
For MyISAM tables you could use BIT(1) fields which get combined together for efficient storage:

As you can see for MyISAM 10 columns take just 7 bytes – less than a byte per column. This is just minimum row length we can have for this table – myisam_data_pointer_size is 6 default plus we need space for delete flag which makes 7 minimum row size MyISAM can have in this configuration.

This trick however does not work for Innodb which allocates 1 byte for each BIT(1) column. So we can get 1 byte per column for boolean flag storage in Innodb (not accounting for standard row overhead) if we use BIT(1), TINYINT or ENUM types but can we do better ?

In fact we can – by using CHAR(0) type (without NOT NULL flag) – this will be pretty much column containing NULL bit only which can store one of two values – NULL or Empty String.

Lets see how these 3 different table format look in Innodb (I’ve populated each with some 2M rows so difference is more visible)

As you can see table which uses BIT(1) column type takes same space as the one which uses TINYINT NOT NULL while CHAR(0) is about 10% smaller. This is modest space savings of course but considering large per row overhead Innodb has this will transform to much larger savings if you have hundreds of such columns.

Lets see how things look for MyISAM for same tables:

As you can see for MyISAM BIT(1) NOT NULL type is as compact as CHAR(0) while TINYINT NOT NULL is a bit less compact.

Looking at results of these tests using CHAR(0) is the most efficient if you would like optimal structure both for MyISAM and Innodb tables, however it is not as convenient to work with. Using NULL as one of flag values means you can’t use normal “=” comparison operator with them:

You can use IS NULL operator which is painful because you need to have different query based on parameter (IS ” would not work) or you can use Null-Aware comparison operator:

Should you go and change all flags to use this approach ? I do not think so – for most applications using TINYINT BIT(1) or ENUM for flags benefit would unlikely be worth the trouble. Due to complication I also would not recommend as a base approach for new applications. However in special cases if you have very many rows and very many flag values to deal with which you can’t pack to the bitmask this approach can be quite helpful.