I need help resolving a retrieval problem I have the following tables. I need to update the download.refer to show the ip_lookup.id value where (download.ADDRESS >= ip_lookup.start_ip AND download.ADDRESS &lt;= ip_lookup.end_ip) AND download.FILENAME is not NULL. The ip_lookup table has more than 5M records with both IPv4 and IPv6 ip's. I need the download.refer field filled for reporting what ip downloaded which files along with country, area, and city. Generating the report in real time with a join takes over 7 minutes. I guess I could add the country, area, and city fields to the download table and use a trigger to fill them when a record is added but that seems like a waste of space. Does anyone have any suggestions? 

CREATE TABLE `download` (
  `ID` int(25) unsigned NOT NULL AUTOINCREMENT,
  `LOG_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ADDRESS` varbinary(16) NOT NULL,
  `FILENAME` varchar(60) CHARACTER SET utf8 COLLATE COLLATE=utf8_general_ci; DEFAULT NULL,
  `refer` int(25) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


CREATE TABLE `ip_lookup` (
  `id` int(25) UNSIGNED NOT NULL AUTOINCREMENT
  `start_ip` varbinary(16) NOT NULL,
  `end_ip` varbinary(16) NOT NULL,
  `continent` varchar(2) COLLATE utf8_general_ci NOT NULL,
  `country` varchar(50) COLLATE utf8_general_ci NOT NULL,
  `area` varchar(50) COLLATE utf8_general_ci NOT NULL,
  `city` varchar(50) COLLATE utf8_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


Thanks in advance

EDIT: This is the query that I used to generate the report that took 7 minutes:

select 
  d.ADDRESS,
  i.country,
  i.area,
  i.city
from download as d
inner join ip_lookup as i on d.ADDRESS 
where (inet6_aton(d.ADDRESS) &gt;= inet6_aton(i.start_ip)  AND (inet6_aton(d.ADDRESS) &lt;= inet6_aton(i.end_ip)) AND d.FILENAME is not null and d.country is null;
varbinary can be used directly in a SQL range:

SELECT d.ADDRESS,
       i.country,
       i.area,
       i.city
FROM download AS d
INNER JOIN ip_lookup AS i
   ON d.ADDRESS BETWEEN i.start_ip AND i.end_ip
      AND length(d.ADDRESS) = length(i.start_ip)
WHERE d.FILENAME IS NOT NULL
  AND d.country IS NULL;


The lengths are a double check they are comparing same IP versions. It may not be needed.

To ensure the ip_lookups are quick

CREATE INDEX ip_range ON ip_lookup (start_ip, end_ip)


If you aren't using the id of ip_range maybe the above should be the primary key.