1

I have a table which includes an IP address:

create table login_history
(
    id         int auto_increment primary key,
    ip         int unsigned,
    created    datetime(6)  not null,
    user_id    int unsigned not null,
);

and another table with an IP range:

create table ip2location
(
    ip_from      int unsigned not null primary key,
    ip_to        int unsigned null,
    country_code char(2)      null,
)

I am trying to join both tables with the following "on" expression.

select * from login_history
left join ip2location_db1  on
    ip2location_db1.ip_from <= login_history.ip_int and ip2location_db1.ip_to >= login_history.ip_int

It's working fine, but it's very slow. How can I improve the performance of such a query? I already added indices on the IP columns of both tables.

Thank you for your help. Have a nice day!

ZenCoding
  • 105
  • 8

1 Answers1

1

One possibility is:

select lh.*,
       (case when ip.ip_from <= lh.ip_int then ip.country)
from (select lh.*,
             (select ip.ip_from
              from ip2location_db1 ip
              where ip_to >= lh.ip_int
              order by ip_to
              limit 1
             ) as ip_to
      from login_history lh
     ) lh left join
     ip2location_db1 ip
     on ip.ip_to = lh.ip_to;

This can then take advantage of indexes on ip2location_db1(ip_to).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thx for that great answer. Is that possible to use in an UPDATE statement? Let's say the login_history has a country column, I want to fill with the information from the ip2country table without losing this performance boost? – ZenCoding Nov 26 '20 at 13:26
  • 1
    @ZenCoding . . . Yes. But this question is about a `SELECT`. You should ask another question about `UPDATE`. By the way, how much better is the performance? – Gordon Linoff Nov 26 '20 at 17:35
  • It cost about a quarter of the time now. I will ask another question. Thanks. :-) – ZenCoding Nov 27 '20 at 08:55
  • I created a new question. I also added a little bit more of the real complexity. https://stackoverflow.com/questions/65034973/mariadb-mysql-update-statement-with-multiple-joins-including-a-ranged-join – ZenCoding Nov 27 '20 at 09:38