Giulio Muscarello Giulio Muscarello - 6 months ago 18
SQL Question

Bitmasks vs. separate columns

I have a database of users and their permissions. For example, a row looks like this:

Name | sendMessages | receiveMessages | post | readPosts
------------+--------------+-----------------+------+----------
Jeff Atwood | 1 | 1 | 0 | 1


What's better for this situation, separate columns (as in the example) or a single column, containing a bitmask (in this case,
1101
translates to
0xD
)?

Answer

tinyint(1) as boolean is usally the best way to go.

Doing queries with bitmask is not efficient as it cannot use index if it has to calculate it or can get very nasty if you try to make use of index

Lets look at simple query

select * from tbl where sendMessages = 1 and readPosts = 1

With single column that would be:

select * from tbl where val&9 = 9

This is not really efficient as it has to do full table scan and calculation.

Lets try to rewrite the query so that it can make use of indexes. This can be done by listing all possible values with IN:

select * from tbl where val in (9, 11, 13, 15)

Now imagine how would this query look if you want to do simple where readPosts = 1

However, if you list too much values mysql optimiser will still do full table scan

Comments