Solved

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

Posted on 2011-02-24
14
1,020 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 34971351
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
ID: 34971492
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 39

Expert Comment

by:Aaron Tomosky
ID: 34971843
Then it may be best to do the city Lookup an just write that to the db.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:bitt3n
ID: 34972267
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 39

Expert Comment

by:Aaron Tomosky
ID: 34972298
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
ID: 34986008
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
ID: 34986020
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
 
LVL 19

Assisted Solution

by:billmercer
billmercer earned 250 total points
ID: 35021608
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
ID: 35022891
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
ID: 35024300
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
ID: 35029623
@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 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 125 total points
ID: 35029788
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
ID: 35032019
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
ID: 35044219
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Horizontal Full Calendar using php 5 42
Data not being replaced when CSV is uploaded 7 43
PHP processing webform 25 44
Load string Array from file 23 41
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

739 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