MySQL Forums
Forum List  »  Performance

Re: Optimize IP Range Join
Posted by: Rick James
Date: June 22, 2008 10:46PM

Most simple approaches take N*M operations -- for each of N ips, check all M ranges. The goal is to check only one thing per N ips.

Probably need INDEX (stop, start). May not need the two indexes you have.

Are your IP ranges non-overlapping? If so, here's something to ponder:
SELECT start WHERE stop >= ip ORDER BY start DESC LIMIT 1;
Then (second step!), if start <= ip, the range is of interest. Hmmm, maybe:
SELECT DISTINCT title FROM ranges,
( SELECT min(stop) as stop, ip FROM ips, ranges
WHERE stop >= ip ) as t
WHERE ranges.stop = t.stop
AND ranges.start <= t.ip
The inner query gathers (in 2N operations) the "stop" values for each ip. The outer one then verifies that "start" works, and de-dups (DISTINCT). Or, so I hope.

Could your IP ranges be represented as aaa.bbb.ccc.000/24? There may be some tricks with masking.

Options: ReplyQuote


Subject
Views
Written By
Posted
3714
June 22, 2008 12:07PM
Re: Optimize IP Range Join
1823
June 22, 2008 10:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.