1

I've downloaded WIPmania's worldip table from http://www.wipmania.com/en/base/ -- the table has 3 fields and around 79k rows:

  • startip // example: 3363110912
  • endip // example: 3363112063
  • country // example: AR (Argentina)

So, lets suppose i'm in Argentina and my IP address is: 200.117.248.17

1) I use this function to convert my ip to long

function ip_address_to_number($ip) {
    if(!$ip) {
        return false;
    } else {
        $ip = split('\.',$ip);
        return($ip[0]*16777216 + $ip[1]*65536 + $ip[2]*256 + $ip[3]);
    }
}

2) I search for the proper country code by matching the long converted ip:

$sql = 'SELECT * FROM worldip WHERE '.ip_address_to_number($_SERVER['REMOTE_ADDR']).' BETWEEN startip AND endip';

which is equivalent to: SELECT country FROM worldip WHERE 3363174417 BETWEEN startip AND endip (benchmark: Showing rows 0 - 0 (1 total, Query took 0.2109 sec))

Now comes the real question.

What if another bunch of argentinian guys also open the website and they all have these ip addresses:

  • 200.117.248.17
  • 200.117.233.10
  • 200.117.241.88
  • 200.117.159.24

Since i'm caching all the sql queries; instead of matching EACH of the ip queries in the database, would it be better (and right) just to match the 2 first sections of the ip by modifying the function like this?

function ip_address_to_number($ip) {
    if(!$ip) {
        return false;
    } else {
        $ip = split('\.',$ip);
        return($ip[0]*16777216 + $ip[1]*65536);
    }
}

(notice that the 3rd and 4th splitted values of the IP have been removed).

That way instead of querying these 4 values:

  • 3363174417
  • 3363170570
  • 3363172696
  • 3363151640

...all i have to query is: 3363110912 (which is 200.117.0.0 converted to long).

Is this right? any other ideas to optimize this process?

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
Andres SK
  • 10,779
  • 25
  • 90
  • 152
  • 1
    If you just throw an index on the worldip column, queries should be lightning fast. I bet queries will complete in under 10 ms. Benchmark what you have, once the index is in place. If you haven't already benchmarked, you're optimizing prematurely. – Frank Farmer Jun 17 '10 at 15:41
  • @Srinivas: i can get those with getenv('HTTP_X_FORWARDED_FOR'); -- just didnt add it to this code yet. @Frank: the thing is that this website has around 3 million visits per month. i cannot benchmark that amount of queries until this code goes live -- that is why i have to optimize prematurely ;) -- i'll add an index though: PRIMARY start end – Andres SK Jun 17 '10 at 15:48
  • I've added benchmark data for 1 query: Showing rows 0 - 0 (1 total, Query took 0.2109 sec) – Andres SK Jun 17 '10 at 15:51

2 Answers2

2

No.

193.150.1.1 - russian IP 193.150.230.1 - swedish IP

You possibly could truncate it to first three octects, but... you wouldn't have that many cache hits. And it's very likely that some /24 network is divided between two contries. Sometimes, blocks smaller than /24 are given out.

Artefacto
  • 96,375
  • 17
  • 202
  • 225
1

Do you absolutely have to use WIPmania? if not, Maxmind offers an open source solution: http://www.maxmind.com/app/geolitecountry. The advantage is that it's a binary file, and there's a PHP extension (you'd have to compile it ans install it). Used it on a couple of projects, the lookups are blazing fast. You can get the PCL extension here: http://pecl.php.net/package/geoip

Maurice Kherlakian
  • 611
  • 3
  • 10