Storing IP addresses

Looking at efficiency

[trillianjedi]

I could have 4 fields:-

IP1
IP2
IP3
IP4

All as single byte values.

Or I could store it as a VARCHAR (15), eg:-

123-123-123-123

If I'm asking MySQL to do a comparison between two, which is likely to be the best performer out of the two?

WHERE `ip` = "123-123-123-123"

or

WHERE `ip1` = "123"
AND `ip2` = "123"
etc....

I assume that MySQL (on a 32 bit OS) does checking in 32 bit chunks, so the first example would actually be completed in a couple of clock cycles and not 15 clock cycles?

Or is there a better way?

What consideration should be given to future upgrade to IPv6 with minimum fuss?

Thanks,

TJ

[trillianjedi]

OK, scrub that, seems MySQL already thought of it (and rather than reinvent the wheel they've just unearthed two standard functions present in the OS TCP/IP stack code).

For anyone else stumbling across this thread looking for an answer:-

MySQL offers:-

INET_ATON() and INET_NTOA()

Which convert 4 byte IP addresses to and from a 32 bit unsigned int.

Perfect...

TJ

[zCat]

Interesting, noted for future reference. The online docu for the current version is this page:

[dev.mysql.com...]

and the functions have been around since MySQL 3.23.15, released in May 2000.

FWIW PostgreSQL has genuine datatypes for IP and network addresses allowing more advanced comparision, e.g. establishing whether an address is in a particular address block, cf:

[postgresql.org...]

[trillianjedi]

Thanks for the links zCat. I'll paraphrase here for ease of reference:-

mysql> SELECT INET_ATON('209.207.224.40');
-> 3520061480

mysql> SELECT INET_NTOA(3520061480);
-> '209.207.224.40'

Nice eh?

Your storage for the field should be set as an unsigned 32bit integer.

I have no idea what's going to happen when we hit IPv6 though - that's one event to remember to watch if you implement something like this.

TJ