Set bingint to 11 for optimal IP storage?

[JAB Creations]

I noticed that when using long2ip on certain integers for IP addresses that many IP addresses were being listed as 127.255.255.255. Clearly the integers were hitting the 32 bit maximum integer of 2130706433.

So after setting the type for IP addresses in the database from int to bigint and tested inserting an IP of 255.255.255.255 the apparent maximum value turned out to be 4294967295 which is 11 characters in length. So I suppose this is a two fold question...

1.) If we're just talking IPV4 then is bigint with a limit of 11 the way to go?

2.) When taking IPV6 in to account how does this effect the optimal maximum length of integers?

- John

[trillianjedi]

1.) If we're just talking IPV4 then is bigint with a limit of 11 the way to go?

You need 4 bytes to store an IP4 address (a 4 byte unsigned INT is the usual way to go). If the limit of 11 you're referring to is for a MySQL DB, I don't think the reference of (11) does anything more than restrict the total number of allowed digits in the INT, eg it does not affect storage size.

2.) When taking IPV6 in to account how does this effect the optimal maximum length of integers?

It will need to become 6 bytes. I don't believe there is an IPV6 version of the INET_NTOA function just yet ?

A BIGINT is 8 bytes, so the capacity is there to store IPV6 when the functions become available.

[JAB Creations]

I'm confused even more now. I just tested inserting an integer greater then the limit so apparently the limit doesn't do jack thus I don't see the point of limit being there in the first place.

Secondly are you suggesting that you need 4 bytes minimally to store an IP4 address? e.g. 0000 without the periods? I don't see how that would actually work since you could have an IP like 255.255.255.255 would be 15 characters.

Isn't bigint 64 bit versus 32 bit? The maximum integer bigint will store is 9223372036854775807 which is 19 characters or 19 bytes.

- John

[brotherhood of LAN]

long2ip

You may want to use the sprintf function for IP's on a class A > 127 to output the IP as an unsigned long.

echo sprintf('%u',ip2long('222.222.222.222'));

255.255.255.255 would be 15 characters.

Stored as text, it is. But since 8 bits/1 byte (2 to the power of 8) can store values from 0-255, you only need 4 bytes to optimally store the value.

IPv4 can have up to 2 to the power of 32 addresses, (more than 4 billion)

IPv6 uses 16 byte addresses (2 to the power of 128) which turns out to be a lot of addresses. Two BIGINT columns is big enough to handle this range.

Edited IPv6 size, as I mistakenly said it was 8 bytes rather than 16

[edited by: brotherhood_of_LAN at 11:06 am (utc) on Nov 30, 2010]

[topr8]

>>I don't think the reference of (11) does anything more than restrict the total number of allowed digits in the INT, eg it does not affect storage size.

>>the limit doesn't do jack thus I don't see the point of limit being there in the first place.

the reference of (11) is only used by client programmes such as phpMyAdmin, it does not actually restrict the number of digits that can be inserted into the database in any way, nor does it effect the size of the storage space allocated

[enigma1]

The reference of 11 means width and it's type dependent. Although it has no effect for the int storage, it does affect the display width when the column is used with the unsigned zerofill attribute. That's the point of it being there for integers. Without the zerofill it doesn't matter for integers.

And phpmyadmin only displays the mysql structure.

[trillianjedi]

Secondly are you suggesting that you need 4 bytes minimally to store an IP4 address? e.g. 0000 without the periods?

Yes, that's the optimal way to store it.

I don't see how that would actually work since you could have an IP like 255.255.255.255 would be 15 characters.

I think your confusion results from thinking in terms of strings of digits, rather than binary representation of numbers.

A byte (8 bits) can store a value between 0 and 255. You can remove the periods and represent an IPv4 address as:-

Byte 1 : [0-255]
Byte 2 : [0-255]
Byte 3 : [0-255]
Byte 4 : [0-255]

The PHP function ip2long and MySQL function INET_ATON take the 15 char string sequence of an IP address with the dots and converts it to the above.

This way it's smaller to strore, and also faster to index or search, as you can use integer maths to do compares, rather than a string compare. As far as anything doing something with it is concerned, it looks and behaves like a 4 byte integer.

If you need it back as a string, you can use the PHP long2ip function, which in effect does something akin to:-

printf("%n.%n.%n.%n", byte1, byte2, byte3, byte4)

IPv6 uses 16 byte addresses

That's right - sorry, my original post incorrectly states 6 bytes. So as stated, you'd actually have to store as 2 BIGINTS, which is possibly why the INET_ATON etc functions are not yet in MySQL or PHP.

I can see a good reason here for a new datastructure type - a superBigInt, of perhaps 16 or 32 bytes.

You could use a 16 byte CHAR, but lack of being to use integer type maths on it may be sub-optimal from a performance point of view.

Going back to your original post:-

I noticed that when using long2ip on certain integers for IP addresses that many IP addresses were being listed as 127.255.255.255. Clearly the integers were hitting the 32 bit maximum integer of 2130706433.

That looks to me as though you've used a singed integer type, rather than unsigned?