Looking for the most efficient database structure for selecting rows from a large table based on one or more true/false values. For example, whether or not a user has opted in to marketing communications.
The use case here is finding user data for all users who have opted in. The Users table contains millions of rows of contact information.
Idea 1: Users table contains an indexed bit field
CREATE TABLE Users UserID INT PK ... OptInFlag BIT
Updating a user's OptIn preference would then just involve setting the OptInFlag to 1 or 0.
Selecting data for all users who have opted in might benefit from the index, depending on opt-in distribution:
SELECT UserID, ... FROM Users WHERE OptInFlag=1
Idea 2: Separate OptIn table that holds FK references to the UserIDs that have opted in
CREATE TABLE Users UserID INT PK ... CREATE TABLE OptIn OptInID INT PK UserID INT FK
Updating a user's OptIn preference would involve INSERTing or DELETEing from the OptIn table. Selecting data for all users who have opted in might benefit from the pre-filtering of the JOIN:
SELECT u.UserID, ... FROM OptIn o JOIN Users u ON o.UserID = u.UserID
Idea 2 looks like it would be the most efficient at first blush, since it effectively pre-filters the Users table before the join, but is that actually the case and are there any potential gotchas with this implementation? For instance, I expect that selecting opted out users would be slower than in Idea 1, but that is OK in this scenario.
Is there an alternative better than either idea 1 or 2?
Edit Ran some tests: Statistics shows the Total Time for idea 1 (using a filtered index) and idea 2 to be about the same. However, the filtered index uses 1/4 the CPU time of the other, showing the winner clearly.