Which is better to control state and perform queries with? One TINYINT column, one BIT(8) column or eight BIT(1) Columns

Question

I pretend to use bitmaps set state (like this guy) and make bitwise queries on my tables. What column types should I use? And how would I perform the selects?

This article got me a little woried about going trough with this idea. I want to be able to index the fields, do joins and everything else I would do with a normal field.

So if I have a table with the lines:

        |1234 5678|
|Id|Name|State    |
|01| xxx|0111 0001|
|02| yyy|1101 1001|
|03| zzz|0101 0011|

I would want to get back the lines that:

StateColumn 234 = 101 and StateColumn 8 = 1 
That would be => (0101 0001)

I should get back the lines with Id 02 and 03.

Is it a good idea to make this kind of searches or am I just crazy?

score 3 · Accepted Answer · answered Feb 12 '11 at 14:19

While a bitmasking approach does have some uses other than impressing friends, (may reduce storage requirements), I strongly advice against using it on data that need to be queried. The reason is that you can't index it efficiently. Most if not all queries have to be resolved using full scans. I was really burned on this one a long time ago, because I tested it on a too small data set while being alone in the database. Add a few hundred thousand rows, a dozen of users and it just doesn't scale up.

Therefore, unless you have some exceptional requirements, I advice you to put each piece of data in its own column (bit or int), along with appropriate indexes (single or compound columns) depending on your query needs.

The "downside" of the (in my opinion correct) approach is increased storage (due to separate indexes) but unless you have millions of rows it's hardly noticable.

If for some reasons that doesn't work for you, there are other options, that exploit patterns in the data to make an efficient search structure. But they all come with a price (severely limited flexibility, locking issues in multiuser environments etcetera).

My advice: Store each piece of data in it own column. This is how the database was intended to be used, and it will leverage all the benefits of a database. This also happens to be the best performing approach in all but the most exceptionally twisted circumstances.

So I should break down my State column instead of having one large bitmask column with different types of data represented on its bits. Probably ending up with this: `|0|000|000|0|` Four bit columns instead of just one. And of course, well planed index on them since I'm valuing speed over storage size. Did I got it right? — GxFlint, Feb 12 '11 at 20:27

score 1 · Answer 2 · answered Feb 12 '11 at 14:26

I want to be able to index the fields, do joins and everything else I would do with a normal field.

"Do joins" implies that you hope to be able to select rows where the 8th bit of the State column in one table matches the 8th bit of the state column in another table.

Don't do that.

Create a column for each distinct attribute. Pick the right data type. Declare all relevant integrity constraints. Index the right columns.

Do that, and you can select and join 'till the cows come home.

Which is better to control state and perform queries with? One TINYINT column, one BIT(8) column or eight BIT(1) Columns

2 Answers2