CODEWITHSUNDEEP
sqlip-address
user4593252
user4593252

Reputation: 3506

Return record with start IP and end IP as range that an IP address falls between?

I have approximately 5.5 million records that contain IP address information. The table has a low end and a high end for each record. Let's call this range ip_start and ip_end. This is mixed IPv4 and IPv6 addresses.

What I need to do is find the one, single record where any given IP address falls between the start and end ranges.

Even taking only IPv4 addresses into account, running the function found here takes a full 58 seconds to run when you convert the passed in IP address and the start and end ranges. This is, quite obviously, unacceptable.

I could do this in C# but I honestly think SQL is going to be faster because I'm using entity framework and I'll be making network calls via WCF... the less times I throw across the wire, the better.

I thought about running a conversion using the function in the previously supplied link and having two indexed bigint columns but then... what in the world would I do with IPv6 addresses? Obviously I can't just convert those the same way because empty sections in the IPv6 address are actually empty while you have a weird "similar" of 0 in IPv4.

Does anyone have any suggestions or places to look?

And just for the sake of reference, the link mention gives us the following function: 

create function dbo.IPAddressToInteger(@ip as varchar(15))
   returns bigint
as
begin
return (
  convert(bigint, parsename(@ip, 1)) +
  convert(bigint, parsename(@ip, 2)) * 256 +
  convert(bigint, parsename(@ip, 3)) * 65536 +
  convert(bigint, parsename(@ip, 4)) * 16777216
)
end
go

Upvotes: 1

Views: 958

Answers (2)

Rick James
Rick James

Reputation: 142298

Even by pre-converging the data and having an index, the query could be quite slow.

If you know that the ranges do not overlay, there is no need to have both a start and end. Instead, use the index to find the start just before your IP, then scan forward for the next entry. Details (in MySQL): http://mysql.rjweb.org/doc.php/ipranges (That covers both IPv4 and IPv6.)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

This is a strategy rather than exact code.

First, update your table to have integer ips. The code is not going to work with the string representation. Here is one method:

alter table t add ipstart_int unsigned;
alter table t add ipend_int unsigned;

update t
    set ipstart_int = dbo.IPAddressToInteger(ipstart),
        ipend_int = dbo.IPAddressToInteger(ipend);

Then create an appropriate index on ipstart_int, ipend_int.

Then, run the query by doing something like:

select top 1 ip.*
from t ip
where ip.ipstart > dbo.IPAddressToInteger(@ip)
order by ipstart asc;

With a bit of luck, this will use the index and be very quick. You can then compare the resulting end ip to be sure that @ip is, indeed, in the right range.

Upvotes: 1

Related Questions