php: showing my country based on my IP, mysql optimized

Question

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? Thanks!

Answer 1

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.

Answer 2

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