What's the optimal way to query data using another table column as a between filter sorry for the ambiguous question title. I don't know what's it called.

The thing is let's say I've a order table storing uid , order_id and ip looks like this 

| uid   | order_id |    ip
| 10000 |    1     | 2098957412
| 10001 |    2     | 2869229574
| 10000 |    3     |  825358912


and another ip table storing ip_from , ip_to and country_code

| ip_from    |   ip_to     |  country_code
| 10000      |   200000    |      GM
| 825358910  |  925358912  |      VN
| 2098957412 | 2198957412  |      FR


I'd like to do something like this 

select order.uid, order.order_id, order.ip, ip.country_code
from order
left join ip
on  order.ip &gt;= ip.ip_from
and order.ip &lt;= ip.ip_to


to have a result like this 

| uid   | order_id |    ip       | country_code
| 10000 |    1     | 2098957412  |     FR
| 10001 |    2     | 2869229574  |    null
| 10000 |    3     |  825358912  |     VN


What's the optimal query to having such a result?
What's the optimal query to having such a result? 


Simple. 

Create proper indices (order(ip) and ip(ip_from,ip_to)).

Complex.

Create a field of LINESTRING type from ip_from to ip_to. Create spatial index (RTree) by it. Use MBRContains for filtering.



From the other side - the ranges (ip_from,ip_to) must be NON-overlapped.

If it is true, then you'll obtain the result with simple

select order.uid, 
       order.order_id, 
       order.ip, 
       ( SELECT ip.country_code
         FROM ip
         WHERE ip.ip_from &lt;= order.ip
         ORDER BY ip.ip_from DESC
         LIMIT 1 ) country_code
from order


Indices ip(ip_from) and order(ip) (or even covering index ip(ip_from, country_code)) is enough for this query.