Showing my country based on my IP, mysql is optimized

Question

I downloaded the wipmania worldip table from http://www.wipmania.com/en/base/ - the table has 3 fields and about 79 thousand rows:

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

So let's assume 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 am looking for the correct 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 lines 0 - 0 (total 1, requests took 0.2109 sec))

Now the real question is .

What if another group of Argentinian guys also opens a site, 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 am caching all sql queries; instead of matching EACH with ip requests in the database, would it be better (and correct) to simply match the first two ip partitions by changing such a function?

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

(note that the 3rd and 4th separated IP address values ​​are deleted).

Thus, instead of querying these 4 values:

• 3363174417
• 3363170570
• 3363172696
• 3363151640

... all I have to request is: 3363110912 (this is 200.117. 0.0 , converted to long).

Is it correct? any other ideas to optimize this process?

Answer 1

Do you absolutely need to use WIPmania? If not, Maxmind offers an open source solution: http://www.maxmind.com/app/geolitecountry . The advantage is that it is a binary file, as well as a PHP extension (you have to compile and install it). Used in several projects, search queries are growing rapidly. You can get the PCL extension here: http://pecl.php.net/package/geoip

Answer 2

No.

193.150.1.1 - Russian IP 193.150.230.1 - Swedish IP

Perhaps you can truncate it to the first three octets, but ... you would not have as many cache hits. And it is very likely that some network 24 is divided between the two countries. Sometimes blocks smaller than / 24 are allocated.