MariaDB/MySQL joining tables on a range

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!

Answer

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