MYSQL 8 Query Join Problem

[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?

[urtrivedi]

You may try following

UPDATE download
LEFT JOIN lookup ON download.ip_address between lookup.start_ip AND lookup.end_ip
SET dowload.ref = lookup.id ;

[Dani]

Yes, I agree with the above poster. You’re doing a left join to include that table, but then you’re additionally doing a superfluous subquery for each row.

[larry29936]

Thanks to both for your reply. Will try your suggestion.

[larry29936]

It didn't work.

    UPDATE download
        LEFT JOIN lookup ON download.ip_address between lookup.start_ip AND lookup.end_ip
    SET dowload.ref = lookup.id; 

Yields the following error:
ERROR 1054 (42S22): Unknown column 'dowload.ref' in 'field list'

EDIT: Just noticed the misspelling in the "SET" statement

[Dani]

In your initial SQL query, you have UPDATE download [...] SET ref = [...]

Are you meaning to update lookup.ref or download.ref? What column are you meaning to update from this query?

[Dani]

Oh, nevermind. There was a typo. download.ref instead of dowload.ref.

[larry29936]

It ran but took 50 minutes. I need to find a faster way to search through 3M records for the correct id for the range that the download.ip_address falls into.

Dani, I'm updating download.ref.

[Dani]

Can you let me know what all the indexes are on both tables?

[larry29936]

Dani, Here's the table descriptions (attached) with examples of the data where appropriate

[larry29936]

Dani, I found this which retreives the id for a single ip address in .0027 sec. :

SELECT t.id
FROM 
  ( SELECT g.* 
    FROM location AS g
    WHERE g.start <=  16785408
    ORDER BY g.start DESC, g.end DESC
    LIMIT 1
  ) AS t
WHERE t.end >= 16785408;

If I could add this to my .php that handles downloads, I wouln't have to do the bulk updates. I have no idea how to turn something this complex into a pdo prepare statement. Is it just a matter of putting it all on one line?

[Dani]

So sorry, quite embarassingly, I don't have experience with prepared statements. All I know is that it helps reduce bandwidth when you have a lot of queries that are all the same except for some slight changes.

[pritaeas]

I have no idea how to turn something this complex into a pdo prepare statement. Is it just a matter of putting it all on one line?

A little late, but yes, that would be enough. Just replace your value with a placeholder so you can bind a value to it before executing.