Brooke Bryan – Make Life Easy, Automate Everything

I have been spending a few days looking into optimisation for a few of our products, the biggest of which is Intellichat, which this great MySQL function is soon to be used for. Intellichat handles thousands of chats every hour, we average at about 190,000 chats a day, which is increasing daily. Every one of these chats holds information about the chat, from the Agent name, through the the IP address that launched the chat. This data allows us to provide detailed statistics to our customers from which Agent name converts best to which Country generates the most sales for their product.

*** Please note that all data sizes below are taken from the PHPMyAdmin row size and not the individual data size as listed in the mysql manual ***

Since starting out, I had always used a char(15) which uses 16 bytes to store ‘192.168.0.1′. Since discovering the INET_ATON function, I can now store the same IP address in just 7 bytes. Here are the results from the 3 main data types you should use for this type of data.

VARCHAR(15) - 20 bytes to store '192.168.0.1'
CHAR(15) - 16 bytes to store '192.168.0.1'
INT(UNSIGNED) - 7 bytes to store '192.168.0.1'

So, if you don’t already know the INET_ATON function, it simply converts the IP address you give it to an integer. This means that when you store 192.168.0.1 into the database, the data value is held as 3232235521. Its a great way to save some space in your database, and you will also have the added benefit of faster searches (looking for IPs within a range can be greatly improved by doing something similar to the following).

mysql> SELECT INET_NTOA(ip) FROM ip_addresses WHERE ip BETWEEN INET_ATON('192.168.0.1')
AND INET_ATON('192.168.0.255');

If you wish to translate the numerical IP back into the IP address you originally saved, you can use the INET_NTOA function, which will just reverse the original function.

Conversion in action:

mysql> SELECT INET_ATON('192.168.0.1');
+--------------------------+
| INET_ATON('192.168.0.1') |
+--------------------------+
|               3232235521 |
+--------------------------+
1 row in set (0.03 sec)

And to convert back:

mysql> SELECT INET_NTOA('3232235521');
+-------------------------+
| INET_NTOA('3232235521') |
+-------------------------+
| 192.168.0.1             |
+-------------------------+
1 row in set (0.00 sec)

One thing to note when using this function is to ensure you use an INT UNSIGNED column. If you used a signed INT you will not be able to store any IP addresses where the first octet is greater than 127. For those of you who dont know what that means, 127.255.255.255 is the last IP address you will be able to store .