ATON || NOTA ? Ip address IPV4 ? IPV6 ?

IP address storage is something that some do right and others have done wrong for years. How many times have you seen or created a MySQL table having something very similar to the following.

CREATE TABLE `example_table` (
  `user_id` int(9)  NOT NULL AUTO_INCREMENT,
  `date_recorded` datetime DEFAULT NULL,
  `ip` varchar(15) DEFAULT NULL,
  PRIMARY KEY `user_id` (`user_id`),
  KEY `date_recorded` (`date_recorded`)
) ENGINE=InnoDB ;

Hopefully you never have and never run across it. What an optimized world it would be if that was the case.  It appears harmless, after all what could it hurt ? You need to store your ip address and it has those pesky periods in it, so why not varchar(15)?  

Human readable ips are just that, just for us. The database has no need to keep them in human readable form. So your field that requires a min 15 characters, to be able to store all of the variations of the ip, and another byte to store the length of the string, now requires ~16 bytes. Your wasting space by storing IP data in varchar fields, it is an integer , store it as an unsigned integer.  They are integers treat them that way.   

CREATE TABLE `table_example` (
  `user_id` int(9)  NOT NULL AUTO_INCREMENT,
  `date_recorded` datetime DEFAULT NULL,
  `ip_address`   INT UNSIGNED NOT NULL,
  PRIMARY KEY `user_id` (`user_id`),
  KEY `date_recorded` (`date_recorded`)
) ENGINE=InnoDB ;

Now just saved you ~6 bytes per row which will add up over time.   The ip address just needs to be inserted with the function INET_ATON. 
example below:

 INSERT INTO table_example VALUES (NULL ,NOW() , INET_ATON('213.136.52.29') ) ;

Pulled back out to test :

select ip_address ,INET_NTOA(ip_address) from table_example  ;

+------------+-----------------------+

| ip_address | INET_NTOA(ip_address) |

+------------+-----------------------+

| 3582473245 | 213.136.52.29 |

+------------+-----------------------+

As you can see the example is simple. As long as you enter and pull information from your tables wrapped accordingly you will save space, and gain speed.  But is it worth it just for 6 to 10 bytes? Yes and the reason is hopefully your database will scale and grow. Those wasted bytes will add up. If you wanted an Index on the ip address field then that will also now be smaller and also faster.

http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_inet-aton
http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_inet-ntoa

What about IPV6 though ? Ah those tricky ips...

Well this appears to be addressed in 5.6.3 

This example taken right from the 5.6 documentation:

SELECT INET6_NTOA(INET6_ATON('fdfe::5a55:caff:fefa:9089'));
        -> 'fdfe::5a55:caff:fefa:9089'

mysql> SELECT INET6_NTOA(INET6_ATON('10.0.5.9'));
        -> '10.0.5.9'

mysql> SELECT INET6_NTOA(UNHEX('FDFE0000000000005A55CAFFFEFA9089'));
        -> 'fdfe::5a55:caff:fefa:9089'

mysql> SELECT INET6_NTOA(UNHEX('0A000509'));
        -> '10.0.5.9'

http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-aton

http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-ntoa