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.

8 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Parvesh

I usually prefer having one single column named flags, and use all it’s bits, one for each boolean. The logic of handling these boolean values can lie either in the program or a mysql function. Size of the column flags can be determined based on the number of such boolean value.

I know this go against the readability of the database, but is good enough if the matter comes to storage.

—
Parvesh

0
Peter Zaitsev

Parvesh,

Sure. If you can pack bits to the bitmask this will be the most efficient as I mention in the end of the article. Though it does not work in all cases.

0
MSDI

I’ve read your comment about the Char trick.

It’s interesting, but I don’t someone should use such thing on a server. It renders the code less readable

WHERE
bIsActive = ” //This means true

WHERE
bIsActive IS NULL //This means false

Also, your trick about the bitmask is fine, but inapropriate if you need to search there values

bIsActive = 1;
bIsLocked = 2;
bIsConstipated = 4;

Someone that has the 3 flags on would be represented as 0000111 (7) in the database, but if you want to extract the list of active people, you’ll need a table scan.

Fine for some use, but if you need index on them, it has a downside…

0
Peter Zaitsev

Sure you’re right. It is very inconvenient for query generation. The bitmap… sure you get searches more complicated and indexes do not work. The more friendly way of bitmaps is SET type though it is hard to alter if you need new bits.

0
Vishnu

Peter,
i tried indexing the char(0) column, but got an error – “The used storage engine can’t index column”. Why is it so?. Also I would like to know if i index the char(0) column will it make any difference in performance (just like other columns) of select queries?

0
Rick James

Vishnu,
It is rarely useful to index a flag.

Only if one value occurs less than 20% (this number varies) of the time, will the optimizer use the index. And it certainly won’t use the index for the other value.

It is sometimes useful to have a flag as part of a “compound” index, especially when this lets the query be performed in the INDEX without hitting the data.

0
Fraser Houchen

A question on this theme.

If you have a tinyint, use it as a boolean – What are the pros and cons of indexing? The table has a 50/50 split of 0/1 and contains 250k records. (INNODB).

0
Osman

Hi! Is this information still correct in MySQL 8? For example, will a table containing four BIT(1) columns take 1 byte or 4 bytes?

0