How do I use flags for more that 64 items is SQL

See more:

SQL

Hi,

Just putting this out there to see if I get any cool ideas. Not sure how I will do this but prolly using a linking table.

NB: I'm sure there are better terms to use here but I can't think of them, so please forgive.

So, My mission was to create a super fast search on 100k to 1 million items using several search criteria (up to 30).
some data is singular (i.e. price in USD: there is only 1 per item)
some data can be in multiples (i.e. available colours: each item can have 0 or more)
some data is singular but can be searched for as a multiple (i.e. each item has 1 size but a search might include size 10 or 12)

I have managed to keep the query super-duper fast by using flags in place of the multiples. Great, so long as the data has fewer that 65 options. The most complex search from ipad to api and back again: < 0.1 seconds!! (with paging and few details, but it gives the impression of being timeless)

Now I have found a category that was singular (store), but now I've been asked to make it multiple. Ok but:
It is now multiple at the item level (each item can be available in multiple stores)
It is now multiple as the search level (each search can include multiple stores)
There are about 150 stores.

Any one of these criteria I could handle, heck give me 2. But all 3 >_<


Some fake data for eg:

with sizes as (
  select 1 as id, 'uk 10' as ukName
  union select 2,  'uk 12'  
  union select 4,  'uk 14'  
  union select 8,  'uk 16'  
), colours as (
  select 1 as id, 'red' as ukName
  union select 2,  'black'  
  union select 4,  'green'  
  union select 8,  'blue'   
), currency as (
  select GUID1 as id, 'USD' as ukName, 1 as ExRt
  union select GUID2,  'GBP' , 1.2
  union select GUID3,  'EUR'  ,1.1
), Items as (
  select Guid1 as Id, 1 as Size, 15 as Colour
  union select Guid2 , 2 , 15
  union select Guid2 , 4 , 7
  union select Guid2 , 2 , 15
  union select Guid2 , 8 , 1
  union select Guid2 , 2 , 3
 ), ItemPrices as (
  select i.Id as ItemId, (10 + i.Size) * c.ExRt as price, c.Id as currencyid
  from Items i, currency c
 )

This is just to show you what the different styles of data look like. The read store search tables has pretty much everything flattened out in tables.

So a search might look as follows

Select i.id
from Items i
inner join itemprices p on i.id = p.itemid and p.currencyid = 'GUID1'
where (Size | 4) > 0 and (Colour | 1) = 1

Imagine that with 30 criteria (but I have managed to be clever with some) and 1Mill items! I'm very proud ^_^


Like I said, just looking for idea's / suggestions. Any new angle would be appreciated.

Sorry for rambling. I hope this makes sense (mostly at least)

What I have tried:

I tried using 2 bigint fields but couldn't work out how to differentiate between (1,2),(2,2) and (2,1). Maybe if I made sure that out of (a,b) a>b?

I also tried CLS aggregates and functions to handle larger binaries than bigint but they were WAY too slow.

My current angle is to go for a linking table, but having a many to many (i.e. each item in many stores, each search including many stores), may slow the query too much.