Member Avatar for 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?

Edited by larry29936
  • 4 Contributors
  • 12 Replies
  • 288 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

A few practical points that pick up on ’s and ’s hints and the fast single-IP lookup you discovered: joining a 2K table to a 3M-range table with an inequality (the BETWEEN/start..end condition) commonly forces a huge amount of row-pair work. The fast pattern is to turn the problem into a small number of indexed seeks — find, per IP, the single lookup row with the largest start that is <= the IP, then confirm its end covers the IP.

A straightforward, usually much faster bulk approach is to set each download row’s ref with a correlated subquery that orders by start DESC and limits to one match. Example (adjust column/table names to match your schema):

UPDATE download AS d
SET ref = (
  SELECT l.id
  FROM lookup AS l
  WHERE l.start <= d.ip_address
    AND l.end   >= d.ip_address
  ORDER BY l.start DESC
  LIMIT 1
);

That executes one indexed seek per download row instead of producing a huge join result. For your ~2K downloads it should finish quickly if lookup.start is indexed.

If you prefer resolving IDs in PHP (no bulk update), use a prepared statement and bind the IP value for each download; that avoids rewriting the same SQL and reduces client/server bandwidth:

$sql = 'SELECT id FROM lookup WHERE start <= :ip AND end >= :ip ORDER BY start DESC LIMIT 1';
$stmt = $pdo->prepare($sql);
$stmt->execute(['ip' => $ip]);
$locId = $stmt->fetchColumn();

Indexing and verification tips: add/verify an index on lookup.start (a composite index (start,end) or (start,end,id) as a covering index can help), then run EXPLAIN / EXPLAIN ANALYZE on the SELECT form to confirm the engine uses an index-seek rather than a full scan. Run updates in small batches or within a transaction to avoid long locks if you need to tweak performance. Finally, ANALYZE TABLE lookup; after large index changes so the optimizer has up-to-date statistics.

Recommended Answers

All 12 Replies

Member Avatar for 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 ;

Member Avatar for 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.

Member Avatar for larry29936

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

Member Avatar for 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

Edited by larry29936
Member Avatar for 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?

Member Avatar for Dani

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

Member Avatar for 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.

Edited by larry29936
Member Avatar for Dani

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

Member Avatar for larry29936

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

Edited by larry29936 because: Attached table descriptions
Member Avatar for 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?

Member Avatar for 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.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.