Need help with mysql trigger

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. This is on a mysql database on my web host. Here’s what I’ve tried:

CREATE TRIGGER before_ins_download  BEFORE INSERT ON download 
    FOR EACH ROW begin
    set new.ip_address = inet_aton(new.`ADDRESS`),
        new.refer=(select `id`
            from `ip_lookup` as i
            where new.ip_address between i.start and i.end
            limit 1   );
END; //

It works without the new.refer part but after I added that it just seems to hang and never gets to the closing DELIMITER; . All ip columns are indexed.

Does anyone see a mistake in my code? Thanks for looking.

EDIT: I was just too impatient. The “between” test has to run against 5 million records.

Sponsor our Newsletter | Privacy Policy | Terms of Service