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 AND lookup.end SET ref = ( SELECT lookup.id FROM lookup WHERE download.ip_address between lookup.start AND lookup.end);
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?