I want a column in my Users table that will keep track of which types of notifications the user wants to subscribe to. There's probably about 10 different types, so I don't want to have 10 boolean columns because this seems kinda hacky and makes adding new types more work. So I'm thinking about using a 32bit integer type and storing the data as a bitmask.
When a certain event happens, let's say event 4, I need to query for which users to notify. So I'll be doing something like:
SELECT UserId FROM Users WHERE Subscriptions & 8;
(I haven't checked this syntax but I'm assuming that's how you do it)..
My question is say there's a million rows in the Users table. If I have an index on Subscriptions, will this index be used in the above query? Is there another good way to make this query super fast, or is my approach totally dumb? I haven't implemented this yet so I'm open to new clever ideas. Thanks!!