TAGS :Viewed: 15 - Published at: a few seconds ago

[ MySQL: storing several boolean values in one column. One tinyint(4) -vs- several tinyint(4) ]

I need to store 5 boolean values in 1 table. Each value could be stored as tinyint(4). So, there are 5 tinyint(4). I'm thinking of putting 5 boolean values in one tinyint(4). I believe, everybody knows even better than me, 5 bits could be saved in 1 byte with no problem:) The first value could be stored as 0(false) or 1(true), the second as 0(false) or 2(true), the third as 0 or 4, fourth as 0 or 8, fifth as 0 or 16. So, if we store the sum of that values in tinyint(4), we exactly know 5 Boolean values.

    For example, stored 21 -> 16 + 4+1.
So, if 21 is stored, we know that:

My question is: Does it make sense to keep only 1 variable? We win db volume (bytes) and performance (4 columns less, but that's only 4 bytes whereas you have actually used varchar(1000) in the same table), but every time we have to "extract" a proper Boolean value from "sum" using php function, and that happens often (let's say when a user presses the button). Does it all make sense to store Boolean values as a sum in 1 column or not, so you have 7 columns instead of 11?

That values, that's clear (because that table has much more rows that just 2), are not keys.

Thank you.

Answer 1

Don't do this -- unless the value is a single "opaque" external data-type, such as a Enum of flags -- if the columns will ever be used in a query or will ever be used outside of said "opaque" type: use discrete/separate fields. (Of the correct type, as jmucchiello and MarkR noted in their answers.)

Trying "for performance" here will just make you loath databases -- and this one in particular -- when you have to "fix" it or work around the ugly scheme later. (If you have a performance problem you'll know it ... and know enough to run a performance analysis before asking.) Donald Knuth was right when he pointed out that 97% of stuff Just Doesn't Matter. So make it pretty and let the database do what it feels like doing.

Happy coding.

If I sound animated above, it's because I'm trying help others avoid the same mistakes I've done run into :-)

Answer 2

No, it does not make sense.

Either store each in its own column, OR use the MySQL-specific SET type, which internally uses bitfields, but is more human-readable.

Worrying about a few bytes per row is really a bad idea. It is a case of incredibly premature optimisation.

Answer 3

Why don't you use the BIT Type and let MySQL worry about optimizing for space?