Have a data model/query queston

thoughts.txt

Okay, so I have a set of ranges.  In the end these will be dates, but for sake of simplicity they're just numbers here.  I think I'll be storing epoch dates.

10-100
30-40
35-60
47-80
12-90

Somebody is going to say "GRAPH DATABASE", but reall what I want is something akin to an index in a SQL db that I can query against ala a where clause.

I have number 36.  How do I store, let alone query *quickly* "Show me all ranges that cross 36?" (the vertical line below)

10---|--------100
  30-|40
   35|--60
      47-80
 12--|-------90

My solution to a similar problem was in SQL. You would start by indexing both ends of the ranges separately, then filter for every lower value less than or equal to your target and every upper value greater than or equal to your target. My solution was a bit more complex, because we were comparing ranges to ranges. In that case, the logic becomes a bit more complex if you need to know if the ranges were super sets, subsets, or overlapping.

Is there a reason (as @bamapookie above said) that you couldn't just store the start and end of ranges? Index them (separately, or in this case I guess no reason not to index them together) and query where start <= $x and $x <= end. Not sure if between() behaves differently with indexes, but if not, then use that.