MySQL Forums
Forum List  »  Optimizer & Parser

Re: How to optimize a range
Posted by: Björn Steinbrink
Date: July 18, 2007 04:52PM

As you have no overlaps, the range you're searching for is the one that has the maximum "start" value that is <= "ip_address".

So you can just search for that range and then check if it really includes the ip address. The search can be done in a simple subquery that MySQL can optimize quite well. It's a dependent subquery, but should still be faster than the table scans.
SELECT
    *
FROM
    ips
LEFT JOIN
    networks ON
        start = (SELECT start FROM networks WHERE start <= ip_address ORDER BY ip_address DESC LIMIT 1) AND
        ip_address <= stop

Should work fine with just an index on networks(start).

Options: ReplyQuote


Subject
Views
Written By
Posted
8714
August 02, 2006 07:56AM
4134
August 04, 2006 09:00AM
3992
July 13, 2007 03:30AM
Re: How to optimize a range
8787
July 18, 2007 04:52PM
3456
December 13, 2008 09:32PM


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.