0

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 >= ip.ip_from
and order.ip <= 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?

  • Is this made up data or is it real? If this data is real, shouldn't there be a BETWEEN somewhere in the query? Note: I am not a networking person! p.s. welcome to the forum! – Vérace Feb 26 at 7:44
  • @Vérace thanks, the logic is real actually. But I think between is equivalent to >= and <= right ? Don't know which one would processed faster anyway – Peem Feb 26 at 7:46
  • @a_horse_with_no_name normally how the IP address should be stored? – Peem Feb 26 at 8:01
  • Is ip an "IP-address"? – Rick James Mar 3 at 4:34
  • @RickJames yes ipv4 – Peem Mar 3 at 9:55
0

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 <= 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.

| improve this answer | |
  • @Peem You will use GEOMETRY and its RTree index for to determine the segment on which the point lies. It is more fast than use common BETWEEN and BTree index. – Akina Feb 26 at 7:52
  • @Peem Shown correlated subquery uses direct search for the constant by index and must be fast. You may create covering index (ip_from, country_code) for to increase the speed one more. – Akina Feb 26 at 7:54
  • @Peem It's better to read the documentation: dev.mysql.com/doc/refman/8.0/en/spatial-types.html – Akina Feb 26 at 7:58
  • The best any BTree index can do is to scan an average of half the ip table. How many rows in each table? – Rick James Mar 3 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.