Solved

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

Posted on 2011-02-24
14
1,013 Views
Last Modified: 2013-12-13
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
Comment
Question by:bitt3n
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
What are you doing with the ip addresses? Just writing like a log is different than lookups jointing on them...
0
 

Author Comment

by:bitt3n
Comment Utility
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
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
Then it may be best to do the city Lookup an just write that to the db.
0
 

Author Comment

by:bitt3n
Comment Utility
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
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
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
 
LVL 2

Expert Comment

by:eZov
Comment Utility
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
 
LVL 2

Accepted Solution

by:
eZov earned 125 total points
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 19

Assisted Solution

by:billmercer
billmercer earned 250 total points
Comment Utility
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
 

Author Comment

by:bitt3n
Comment Utility
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
 
LVL 2

Expert Comment

by:eZov
Comment Utility
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
 

Author Comment

by:bitt3n
Comment Utility
@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
 
LVL 38

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 125 total points
Comment Utility
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
 
LVL 19

Assisted Solution

by:billmercer
billmercer earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:bitt3n
Comment Utility
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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now