Maximum length of the textual representation of an IPv6 address

Whatever you do, make sure not to trust data sent from the client. $_SERVER['REMOTE_ADDR'] contains the real IP address of the connecting party. That is the most reliable value you can find.

However, they can be behind a proxy server in which case the proxy may have set the $_SERVER['HTTP_X_FORWARDED_FOR'], but this value is easily spoofed. For example, it can be set by someone without a proxy, or the IP can be an internal IP from the LAN behind the proxy.

This means that if you are going to save the $_SERVER['HTTP_X_FORWARDED_FOR'], make sure you also save the $_SERVER['REMOTE_ADDR'] value. E.g. by saving both values in different fields in your database.

If you are going to save the IP to a database as a string, make sure you have space for at least 45 characters. IPv6 is here to stay and those addresses are larger than the older IPv4 addresses.

(Note that IPv6 usually uses 39 characters at most but there is also a special IPv6 notation for IPv4 addresses which in its full form can be up to 45 characters. So if you know what you are doing you can use 39 characters, but if you just want to set and forget it, use 45).

I found myself asking this question and from all the posts I read never found any performance comparisons. So here's my attempt.

I've created the following tables, populated with 2,000,000 random ip address from 100 random networks.

CREATE TABLE ipv6_address_binary (
    id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    addr BINARY(16) NOT NULL UNIQUE
);

CREATE TABLE ipv6_address_twobigints (
    id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    haddr BIGINT UNSIGNED NOT NULL,
    laddr BIGINT UNSIGNED NOT NULL,
    UNIQUE uidx (haddr, laddr)
);

CREATE TABLE ipv6_address_decimal (
    id SERIAL NOT NULL AUTO_INCREMENT PRIMARY KEY,
    addr DECIMAL(39,0) NOT NULL UNIQUE
);

Then I SELECT all ip addresses for each network and record the response time. Average response time on the twobigints table is about 1 second while on the binary table it is about one-hundredth of a second.

Here are the queries.

Note:

X_[HIGH/LOW] is the most/least significant 64-bits of X

when NETMASK_LOW is 0 the AND condition is omitted as it always yields true. doesn't affect performance very much.

SELECT COUNT(*) FROM ipv6_address_twobigints
WHERE haddr & NETMASK_HIGH = NETWORK_HIGH
AND laddr & NETMASK_LOW = NETWORK_LOW

SELECT COUNT(*) FROM ipv6_address_binary
WHERE addr >= NETWORK
AND addr <= BROADCAST

SELECT COUNT(*) FROM ipv6_address_decimal
WHERE addr >= NETWORK
AND addr <= BROADCAST

Average response times:

Graph:

BINARY_InnoDB  0.0119529819489
BINARY_MyISAM  0.0139244818687
DECIMAL_InnoDB 0.017379629612
DECIMAL_MyISAM 0.0179929423332
BIGINT_InnoDB  0.782350552082
BIGINT_MyISAM  1.07809265852