Re: WHERE clause with 40+ conditions

On 5 May 2012 00:01, DrYSG <ygutfreund(at)draper(dot)com> wrote:
> My users are presented with a list of checkbox for 4 different attributes
> (e.g. type of image, country, etc.)
>
> Suggestions?

Not as a DB guy, but as a software guy, I might implement something
along these lines:

Create a bit array for each of the attribute, of sufficient length.
Then assign one bit for each value. Assuming this is an image search
system, and you want to search for PNG, JPG and GIF images, create a
3-bit array, with bit 0 turned on means include PNG in search, bit 1
turned on means include JPG in search and bit 2 turned on means
include GIF In search. So, based on what the user selects, you will
get a 3-bit number for image format. Similarly, create n-bit numbers
for each of the other attributes. At the end, concatenate all of them
to get one large N-bit number.

Also, at the time of storing an image, I would populate such an N-bit
number for each image, based on the attributes that are set for that
particular image.

Now, my query will have just one WHERE clause which would look like:

WHERE bitfield_stored_in_db & bitfield_from_search_form <> 0;

Binand