Searching IP Ranges Efficiently PHP and MySQL

Question

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.

Holy smokes, why do you have 100k IP addresses that you're banning?? I'm not sure if there really is a way to efficiently search through 100k records on every single HTTP request. — Hayden Schiff, Oct 13 '16 at 17:06
Make sure you have an index on either `ip_from` or `ip_to` (I don't think indexing them both will make it better). — Barmar, Oct 13 '16 at 17:08
If you are using ip2location, as suggested by your query, why don't you use their BIN format which is much more efficient and faster? — Hanky Panky, Oct 13 '16 at 17:08
@HankyPanky https://www.ip2location.com/databases/px2-ip-country is the database being used. I don't believe they have a BIN format for it. — Belgin Fish, Oct 13 '16 at 17:14
@Barmar Unfortunately there's already an index, I'm starting to think I won't be able to get any more efficiency. — Belgin Fish, Oct 13 '16 at 17:14
since you probably won't ever need to check every single possible address. so you could try superficial performance improvements by caching your results. a `WHERE ip = :ip` could prove to be quicker than a between-lookup — Franz Gleichmann, Oct 13 '16 at 17:15
100k is small if indexed. You can also do an ip block cidr search as a separate angle for some applications. If you save you ip addr in other columns (other formats) and index them, that range will be a snap. See uint_from_ip columns in this answer of mine http://stackoverflow.com/a/32020220 — Drew, Oct 13 '16 at 17:21
Please run a `explain select` for that query and add the result to your question. Then, you might try `SET profiling=1; SELECT ip_from FROM ip2location_proxy WHERE '".$ip."' BETWEEN some_ip AND some_ip LIMIT 1; SHOW profile;` — Alfabravo, Oct 13 '16 at 17:25
@HaydenSchiff According to https://www.ip2location.com/databases/px2-ip-country it should be ~500,000 rows but maybe OP trimmed the data. — MonkeyZeus, Oct 13 '16 at 17:27
Also, BETWEEN is just equivalent to a < some < b according to docs. So, it's not about that [(see this).](http://stackoverflow.com/questions/4382892/whats-mysqls-between-performance-over#4382919). Last thing: have you checked if load is on MySQL or PHP? Depending on your server, you might have negative values and we don't know how is that comparison going... [something about ip2long and mysql](http://stackoverflow.com/questions/11556791/is-ip2long-in-php-equal-to-inet-aton-function-in-mysql#11556801) — Alfabravo, Oct 13 '16 at 17:31
IPv4 addresses are just one kind of IP address. IPv6 is a thing and you ignore it at your own peril. — tadman, Oct 13 '16 at 20:51
@Alfabravo I will execute your recommendations tomorrow and post back. — Belgin Fish, Oct 14 '16 at 02:22

MonkeyZeus · Answer 1 · 2016-10-13T17:25:17.943

1

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

edited Oct 13 '16 at 17:25

answered Oct 13 '16 at 17:20

MonkeyZeus

20,375
4
36
77

Unfortunately the index starts at basically 0. This was a good suggestion though. – Belgin Fish Oct 14 '16 at 02:23

score 1 · Answer 2 · answered Oct 13 '16 at 17:24

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

Searching IP Ranges Efficiently PHP and MySQL

2 Answers2