Detecting users country using IP Address - Performance Issues?

[username]

Hi all,

I am building a basic ad serving piece of software that needs to serve different ads across different countries. One of the requirements is to check the country the user is coming from, and based on the result serve an appropriate ad. The problem I have come into is that I found at [ip-to-country.webhosting.info...] that the IP ranges total 87,000+ records, which in turn would be pretty heavy to go through in a database to cross reference them against a user's IP every time I want to render an ad. Is there another way to do this, or is there some great free software that can be used to plug into a PHP app to perform this essential funtion?

Thanks in advance.

[Anyango]

Its not all that resource intensive, they would have a field that is a Number unique for every ip address and that makes the searching very fast. that number can also be computed very easily if you have to do it yourself when creating the database table.

[username]

True, but from what I hear a mysql db table can only hold 65,000 rows, so I would need to split the data to do this? Is this really the best approach? I am still not convinced. There must be a plugin or php quick fix?

[vincevincevince]

You can hold many more than 65k rows with MySQL... not sure where you get that idea from. It sounds suspiciously like the Microsoft Excel 65,536 row limit.

To reduce performance demands, try not to require country on the homepage / landing page. As soon as a visitor arrives on the homepage, start a background request and then cache the result. When they get to the page upon which you need the country, it's already ready and available immediately.

You can write the country and user's current IP to a cookie to avoid triggering a lookup twice; something like:

$cookiestring[]=ip2long($ip); //integer IP
$cookiestring[]=$countrycode; //the country code from lookup
$cookiestring[]=substr(md5("salt",implode("¦",$cookiestring),5,10); //a short hash of the first two + salt to stop meddling
set_cookie("geolocation",implode("¦",$cookiestring)...); //set expiry & domain as required

Reading that again - just explode by ¦ - then test whether the hash part ([2]) matches a recalculation from [0] and [1].

[Anyango]

As soon as a visitor arrives on the homepage, start a
background request and then cache the result

Hey 3Vince
I would love to know how to do the background request stuff in a simple way , can you please give me some direction

[edited by: Anyango at 9:40 am (utc) on Nov. 29, 2008]

[vincevincevince]

Simple and background request are two concepts which don't mix well when it comes to PHP. Here's a simple way to do it on a linux or unix system:

exec("php /path/to/background_lookup.php $ip > /dev/null 2>&1 &");

i.e. start a background process with all output to /dev/null which does a lookup. Note the argument is a 'command line argument' to pass the IP.

[Anyango]

so if background_lookup.php script took 10 seconds to finish it won't pause my calling script from execution right ?

[vincevincevince]

Not if it was started in that way with exec

[username]

Thanks Vince, looks like not a bad option. To simplify I will:

1. Insert all IP records into db table
2. When page loads run query on ip table once to attain country
3. Store in geolocation cookie

..Upon return to site:

4. Check if cookie exists,
5. If it does, use that country
6. Else go back to step 2.

Seems like a simple but solid structure. Have I missed anything?

[GaryK]

FWIW, I've got an IP to Location DB with three million plus rows and there are rarely any performance issues. Just be sure to index the lookup column(s) for peak performance and you should be good to go.

[tgotchi]

We are using the IP2Location database from [ip2location.com...] . The database has been optimized for fast query. A simple SQL query using one ipFROM column has speed up the query tremendously.