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: