Re: How to optimize a range

[Björn Steinbrink]

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).