SQL Query- Where Value Between Two Columns

Imagine a simplified sample table that contains id, low_range, high_range. Low_range and High_range can be decimal ip address, zip codes or even timestamps.

id	low_range	high_range
1	400002	400120
5	407049	407340
5003	637190	637805
702984	849380	849875

We need to find row where a number is greater than low_range and lesser than high_range. Assumption is that maximum of one record and minimum of zero will be returned. 

SELECT id FROM table WHERE num > low_range AND num < high_range. 

The sql above might work well when we have probably few hundreds rows. But it is extremely slow with just few thousands records. Adding index on min_range (or / and max_range) will be helpful only for last few (or first few) records in index. For rest of rows, engine will consider a full table scan to be faster compared to using index. 

Solution 1:

SELECT id
FROM tab tab,
( SELECT max(low_range) as max_low_range
           FROM tab WHERE low_range <= num ) low_range_table
WHERE table.low_range = low_range_table.max_low_range
AND full_table.high_range >= num;

While above query might look complex to human, it makes life easier for database engine.   It first finds max low range available lesser than number. We have index on low_range; so this sub query will be fast. Next it joins with full table using this low_range. (Ideally, we should join with primary key). This will give exactly one row. Finally it validates if high range is still higher than number / string.

Explain plan:

Solution 2: 

          SELECT id
          FROM (
                    SELECT * FROM tab tab
                    WHERE low_range <= num
                     ORDER BY low_range DESC LIMIT 1) as max_low_range
          WHERE max_low_range.high_range >= num

Explain Plan