CODEWITHSUNDEEP
mysqlgeoip
Michael Sweetser
Michael Sweetser

Reputation: 69

Subquery and Indexing for IP Information

I'm having a problem trying to cross-reference between a table with user information and a table with geolocation data (from GeoIP's database).

I have the IP address in standard format (not integer) in the user table, and the GeoIP data in the GeoIP table, with the integer IP ranges.

This query works, but is very slow and unoptimized.

SELECT email, country 
FROM users 
INNER JOIN geoip ON users.ip BETWEEN geoip.startip AND geoip.endip

I feel like I'm missing something very easy here.

UPDATE: This query works, but is very slow - is there any way to index it to make it go faster? Right now, no matter when run, each row would take about 300-500ms to execute, which is way too slow.

SELECT email, country 
FROM users INNER JOIN geoip ON INET_ATON(users.ip) 
BETWEEN geoip.startip AND geoip.endip

Thanks!

UPDATE 2: Here's the EXPLAIN output on the query:

+----+-------------+-----------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | geoip     | ALL  | NULL          | NULL | NULL    | NULL |  3651972 |             | 
|  1 | SIMPLE      | users     | ALL  | NULL          | NULL | NULL    | NULL | 87996123 | Using where | 
+----+-------------+-----------+------+---------------+------+---------+------+----------+-------------+

I can't add an integer-only IP row right now because the DB's in use and it's over 90 million rows; it'll be something I look into doing during some downtime, but right now, I'd like to get it running this way.

Upvotes: 0

Views: 432

Answers (1)

Fox
Fox

Reputation: 2368

I cannot comment yet, so here's an 'answer' ...

Are you sure it works? If I understand your description correctly, you have users.ip as CIDR notation in char or varchar and geoip.startip/endip as integer. As such, this query has no way to compare these two correctly.

Correct way of doing this would be either

SELECT email, country 
FROM users INNER JOIN geoip ON INET_ATON(users.ip) 
BETWEEN geoip.startip AND geoip.endip

or

SELECT email, country 
FROM users INNER JOIN geoip ON users.ip 
BETWEEN INET_NTOA(geoip.startip) AND INET_NTOA(geoip.endip)

which is the better one pretty much depending on which table is larger (more rows).

The best way to do this, though, would be to store users.ip as integer (or another column with integer interpretation).

Upvotes: 2

Related Questions