Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1045
  • Last Modified:

what's the optimal way to store ip addresses including both ipv4 and ipv6?

I'm currently storing IP addresses in MySQL as varchar(39) as per suggestions at

http://stackoverflow.com/questions/1076714/max-length-for-client-ip-address

but I'm curious whether there might be a better way to do it, such as a pair of functions that codes/decodes ipv4/6 to/from integers.

this page

http://bafford.com/2009/03/09/mysql-performance-benefits-of-storing-integer-ip-addresses/

suggests that might be a good idea. I am wondering if it is worth it, and if a reliable pair of PHP functions exists to translate them (since ip2long appears to be just for ipv4).
0
bitt3n
Asked:
bitt3n
  • 5
  • 4
  • 3
  • +1
4 Solutions
 
Aaron TomoskySD-WAN SimplifiedCommented:
What are you doing with the ip addresses? Just writing like a log is different than lookups jointing on them...
0
 
bitt3nAuthor Commented:
I've got a table of products, and when a user views information for a given product, I store the user's IP in case it might be interesting at some point to see the aggregate locations of users who view a given product. (For example, perhaps product X is popular in Los Angeles but not in Chicago.) Contrawise, it might be interesting to see a list of products that are popular in a given area, which products might then be displayed to other visitors whose ip addresses indicate they are in that area.
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
Then it may be best to do the city Lookup an just write that to the db.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
bitt3nAuthor Commented:
True, but I'm not sure city is how I'll want to do the breakdown. I might also want to do it by state, region (New England, for example), country, or distance. I won't know that until I have enough data to see how localized people's preferences are, and this might vary by product. I'm also not 100% sure that I will not come up with some other use for the IP addresses (for example, I might want to delete a user's session if his IP address changes), so it seems prudent to store the addresses themselves.
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
If you are not constantly using the ipaddress to look stuff up, it really doesn't matter all that much how you store it aside from taking up space.
0
 
eZovCommented:
MySQL borrows the rfc defined functions for INET (ipv4) from C, and can be used as follows:
SELECT INET_ATON('209.207.224.40');

and back from number:
INET_NTOA(expr)

Given a numeric network address in network byte order (4 or 8 byte), returns the dotted-quad representation of the address as a binary string.

mysql> SELECT INET_NTOA(3520061480);
        -> '209.207.224.40'

You can see this at http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton
0
 
eZovCommented:
You have here another useful link regarding IPv4 and IPv6:
http://return-true.com/2008/08/storing-ip-address-data-in-a-mysql-database/

If you want to limit your fields for a little more optimisation then the IPv4 encoded length is 32-bit and the IPv6 length is 128-bit. That’s 4 byte and 16 byte.
0
 
billmercerCommented:
True, but I'm not sure city is how I'll want to do the breakdown.
If you're planning to store the IP address for a long time then do lookups on it later on, you may want to reconsider. IP address spaces can and do change, and an address that gives you one location one day might give another location six months later. If you want to capture geoip data, your best bet would be to identify the location at the time of purchase and store that, rather than the IP address alone.  
0
 
bitt3nAuthor Commented:
interesting.. do the locations change markedly? for example, for a Miami IP address to change to a Ft. Lauderdale IP 6 months later isn't a big deal, but if it changes to Detroit, that would certainly make storage of IP addresses for regional info useless, as you suggest.
0
 
eZovCommented:
What Are the Limitations of Geolocation?
IP address location databases have greatly improved in accuracy over the years. They may attempt to map each network address to a specific postal address or latitude/longitude coordinate. However, various limitations still exist:

    * IP addresses may be associated with the wrong location (e.g., the wrong postal code, city or suburb within a metropolitan area).

    * Addresses may be associated only with a very broad geographic area (e.g., a large city, or a state). Many addresses are associated only with a city, not with a street address or latitude/longitude location.

    * Some addresses will not appear in the database and therefore cannot be mapped (often true for IP numbers not commonly used on the Internet).

How accurate is GeoLocation?

Geolocation is the practice of identifying the physical, real world position of a person, tool or subject matter using digital information processed through the net or other electronic means of communication.

Geolocation can detect region, city and ZIP code from which someone is or has connected to the www by using their device’s IP address. Accuracy rates on getting a city from an IP address vary between 60 and 90 percent, according to DNS Stuff.

Even when not accurate, geolocation can put users in a bordering or nearby city, which could be good enough for the entity seeking the info. Business geolocation service providers are continually developing and discovering new ways to develop their information databases, and as a result their accuracy.
0
 
bitt3nAuthor Commented:
@eZov that's useful but it doesn't actually say whether address re-allocation might drastically change the location assigned to an IP. My guess is it probably doesn't, but I'm asking around to see if I can find out for sure. I'll report back here with what I learn.
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
Even if the answer is that the don't usually change now that ipv4 address are all allocated, I bet they move much more and further than normal.
0
 
billmercerCommented:
An actual reassignment of a block of addresses from one physical location to another is rare, but that's not the only possible issue.

The notion that a person's internet presence is tied to a specific IP address is becoming less and less relevant as more and more people use mobile broadband. ISPs merge or  go out of business. Companies change service providers. Finally, the databases that are used to perform geolocation are constantly being updated. The more time passes between when you get the IP address and when you do the lookups, the more likely it is that you'll be getting something different than you would have at the time of the order.

However bear in mind also that even the most accurate databases are not all that accurate. Geolocation is very accurate for identifying a country, and quite good still at the state/province level, but when you get down to the city level, it's much less accurate. Anyone who tells you they can give you city or longitude/latitude with 90% accuracy is probably lying, 60-70% is more typical.


Revisiting your original question, if you are planning on doing geolocation, how you store the addresses may also depend on how you're doing the lookups. If you plan on buying a database for local use, then you may want to look at the API for the system you buy and make something that works with that. If you plan on using a web service, it probably doesn't matter as much. Performance-wise, the overhead will probably be mostly in the lookup itself, and size-wise, unless you're eBay or Facebook, 39 bytes is pretty trivial.

0
 
bitt3nAuthor Commented:
thanks for everyone's input, I'm going to store them as varchar for now and optimize later if necessary, assuming most (maybe 80-90%) addresses will be accurate to the state level for at least a few months, which is good enough for my purposes.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now