MySQL: Storing IPs as int instead of varchar

I read an interesting article about storing IP addresses as ints instead of a string using MySQL’s built-in conversion functions.

Another good find from the PHP Freaks MySQL Sticky, Join-Fu: The art of MySQL tuning. Instead of storing IPs as a 15 character varchar, we can instead store them as an INT.

When we want to insert an IP address to just use this function to convert it to an int:

INSERT INTO table (ip_address) VALUES (INET_ATON('192.168.1.112'))

This will convert the IP address to an integer value. When we want to retrieve this info from the table just use this statement:

SELECT INET_NTOA(ip_address), ip_address FROM table

This will show you the actual IP address as well as the integer converted IP address.

So we go from a 15 character varchar (15 bytes) to an INT (4 bytes) for a total field space savings of 73%. This is a big savings as your table size increases.

Continue Reading