CODEWITHSUNDEEP
phpmysql
Belgin Fish
Belgin Fish

Reputation: 19837

Searching IP Ranges Efficiently PHP and MySQL

I have a database with roughly ~ 100,000 entries of IP ranges which my website will reject requests from.

The table storing these ranges is in the format

ip_from (int)
ip_to (int)

Using PHP I convert the users IP to the integer value using ip2long

I then use the following SQL query to determine whether the users IP is present in a range in the table.

SELECT `ip_from` 
FROM `ip2location_proxy`
WHERE '".$ip."' BETWEEN ip_from AND ip_to LIMIT 1

The issue is that this is creating large server load. I'm wondering if anyone can suggest a better method for detecting if the IP is within a range specified in the database, such as an alternative to using the BETWEEN command.

Upvotes: 0

Views: 395

Answers (2)

MonkeyZeus
MonkeyZeus

Reputation: 20737

Assuming that your indexes are good and you are running on a not-so-low powered or slow disk access system then I suggest the following:

If you find that you have a large whitelist range such as 0 - 2147483648 then you can try making sure the IP is not in the whitelist range first and then consult your DB.

<?php
if($ip > 2147483648)
{
    // consult the ip2location_proxy table
}

Also, looking at the example data on https://www.ip2location.com/databases/px2-ip-country, maybe you can try removing the unneeded columns which are not ip_from and ip_to

Upvotes: 1

Andrew Edvalson
Andrew Edvalson

Reputation: 7878

We've had similar issues with BETWEEN. Try using two separate queries with a UNION. Honestly, I don't really know why this was fast while BETWEEN was slow.

SELECT `ip_from` 
FROM `ip2location_proxy`
WHERE '".$ip."' >= ip_from

UNION ALL

SELECT `ip_from` 
FROM `ip2location_proxy`
WHERE '".$ip."' <= ip_to

Upvotes: 1

Related Questions