Paul
Posted Dec 11th, 2011
Tagged dba, mysql, ruby, performance

Storing IP addresses the smarter way

If you need to store an IP address in a database, it's possibly because you're logging web requests or similar. This means the table can get very large, very quickly. If you're storing the addresses in a VARCHAR, you will use up a lot of space in your table and make indexing a nightmare. A lot of people don't know that you can represent these as an INT which is much more db-friendly.

IPv4 addresses are just 32-bit integers. In most languages (including in MySQL itself, useful for Sphinx indexes) you can use the equivalent of inet_ntoa(3) to convert to string and inet_aton(3) to convert to an int. In Ruby however this is hidden inside the IPAddr class. This instantiation can be quite expensive for what is really a simple conversion, so you can use the following:

def inet_aton ip
  ip.split(/\./).map{|c| c.to_i}.pack("C*").unpack("N").first
end
def inet_ntoa n
  [n].pack("N").unpack("C*").join "."
end

Storing these correctly can save huge performance headaches later.