Performance issue on MySql join where value between 2 columns

I have two tables. The ipaddress range table is in the following format:

ip_start BIGINT
ip_end BIGINT
country VARCHAR

(IP addresses have been converted to a number)

The second table contains the order details and the users ip_address also in the same number format.

I am trying to return the orders table together with the country of the ipaddress.

Doing the following join works, but the query takes several minutes on about 5000 rows.

SELECT *  FROM  orders o
LEFT JOIN iplookup ip
  ON o.ip_address >=  ip.ip_start AND
     o.ip_address <= ip.ip_end

How can I improve the performance of this query?


Source: mysql

Leave a Reply