Mysql 8 query problem

act78723 · August 16, 2020, 2:46pm

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?

benanamen · August 16, 2020, 5:04pm

If you’re trying to get data then you shouldn’t be doing any update. An SQL dump of your DB schema and a few sample records would be helpful in helping you.

act78723 · August 16, 2020, 5:54pm

I’m updating the ref column of existing records in the download table with the id of the record in the lookup table which the ip in the download table falls between. Schema follows:

mysql> show columns from download;

±-----------±-------------±-----±----±------------------±------------------+
| Field | Type | Null | Key | Default | Extra |
±-----------±-------------±-----±----±------------------±------------------+
| id | int | NO | PRI | NULL | auto_increment |
| log_time | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| address | varchar(40) | NO | | NULL | |
| ip_address | int unsigned | YES | MUL | NULL | |
| filename | varchar(60) | YES | | NULL | |
| ref | int unsigned | YES | | NULL | |
±-----------±-------------±-----±----±------------------±------------------+