Author: larry29936

I have the following query which has died after running over an hour on my local mysql 8 server:

UPDATE download
  LEFT JOIN lookup ON download.ip_address between lookup.start_ip AND lookup.end_ip
SET ref = (
    SELECT lookup.id FROM lookup WHERE download.ip_address between lookup.start_ip AND lookup.end_ip);

All ip fields are IPV4 and stored as unsigned integers and are indexed. The lookup table is approx. 3M rows, relating ip ranges to country, area, and city. The download table is approx. 2K rows. What I'm trying to do is get the id from the lookup table row that has the ip range that the download ip_address falls into.

Does someone see a problem with the query?

I'm trying to create a trigger that does two things, first take an ip that's in dot notation and run inet_aton on it and put the result in another field. Second, checks a lookup table to identify an ip range that the result of the first action falls into and enters the id of that row in the table. Do I have to break this down to separate triggers, a before insert and an after insert? This is a mysql database.

DELIMITER //
CREATE TRIGGER download_ins  BEFORE INSERT ON download 
 FOR EACH ROW begin
   declare vip varbinary(16);
   select `ADDRESS` from `download`;
   vip = inet_aton(`ADDRESS`);
   set NEW.ip_address = vip;

   declare vrefer int(25) unsigned;
   select id
  from `ip_lookup`
  where NEW.ip_address between ip_lookup.start and ip_lookup.end
  limit 1; 
  if vrefer is not null then
    set NEW.refer = vrefer;
  else
    exit;
  end if;
END; //

DELIMITER ;

Thanks in advance