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
