Hexadecimal binary search filter mask

[samuelgarcia87]

I have been told to build some new databases for a website and I come across the following problem:In one of the databases, every row contains currently 15 "features", being columns ft1, ft2, ft3 and so on, all as binary 1 or 0 values. At the moment it works ok my search queries are as follows:In this case I want to retrieve rows that have features 3 6 and 12:

SELECT * FROM  `feature_link` WHERE ft3 =1 AND ft6 =1 AND ft12 = 1;

The problem is that the number of features will rise soon to many hundreds, and I think this is not efficient so I want the following:As an example, having 16 features could be stored in a 0 and 1 bitmask of 16 bits so a row with features 3, 6 and 12 would be 0000100000100100 as a feature set mask then if looking for feature 3 and 6... the result should come up with all the results that have a 1 in positions 3 and 6... It would be some kind of bitmask operation as follows.A matching string returns the same searchterm:0000100000100100 (0x824)AND0000000000100100 (0x024)=0000000000100100 (0x024)But a non matching string returns a different one:0000100100000100 (0x904)AND0000000000100100 (0x024)=0000000000000100 (0x004)Is there some data table structure and/or query I must use to implement this kind of search system?And furthermore, if the feature bitmasks work... could they be stored in hexadecimal format so the URI argument would not be that large.. something like ?features=824 where 824 would be the bitmask in hexadecimal encoding. So as an example, features 3,6 and 12 would be The resulting query should be something like:

SELECT * FROM  `feature_link` WHERE (featureset AND 0x824)=0x824

Thanks in advance for any help.

[justsomeguy]

Yeah, you should be able to use binary values like that and binary operators. How you do would depend on which database you're using, but it should have a way to store binary data and binary operators to work with it.

[jeffman]

This article might help: http://consultingblogs.emc.com/jamesrowlan...al-example.aspx