IP-addr to Country: JOIN question

#11

On 16-09-10 18:14, EricFilson wrote:

Quote:

On Aug 31, 4:55 am, Lennart Jonsson<erik.lennart.jons... (AT) gmail (DOT) com
wrote:
On 2010-08-30 12:21, Andrew C. wrote:
[...]

However, I would like to perform a similar lookup for all IP addresses in
the 'addresses' table I mentioned earlier (the one with the list of IP
addresses in the 'ip' column)... but I can't work out how to JOIN the
'addresses' and 'countries' tables using the 'WHERE (ip-to-lookup)<= ip_to
ORDER BY ip_to LIMIT 1' type approach, assuming there's even a way of doing
this.

Thanks in advance for any advice on how to get this approach to work when
JOINing twoo tables, or even for any suggestions on a better approach to
looking up country code via IP address! :-)

I assume countries is a rather small table so there should be no problem
adding indexes like:

create index ... on countries (ip_from, ip_to) ...
create index ... on countries (ip_to, ip_from) ...

I imagine a query like:

SELECT a..., c.code
FROM countries c
JOIN addresses a
ON a.ip_address between c.ip_from
and c.ip_to

will perform reasonable well

/Lennart

No, it does not... 100 rows in 13.5s (I'm having the same issue)

more than 13s is way too long.... ;-)

can you post the output of:
SHOW CREATE TABLE countries\G
SHOW CREATE TABLE addresses\G
SELECT * FROM countries LIMIT 15;
select * from addresses LIMIT 15;

--
Luuk

#12

On 2010-09-16 18:14, EricFilson wrote:

Quote:

On Aug 31, 4:55 am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com
wrote:
On 2010-08-30 12:21, Andrew C. wrote:
[...]

However, I would like to perform a similar lookup for all IP addresses in
the 'addresses' table I mentioned earlier (the one with the list of IP
addresses in the 'ip' column)... but I can't work out how to JOIN the
'addresses' and 'countries' tables using the 'WHERE (ip-to-lookup) <= ip_to
ORDER BY ip_to LIMIT 1' type approach, assuming there's even a way of doing
this.

Thanks in advance for any advice on how to get this approach to work when
JOINing twoo tables, or even for any suggestions on a better approach to
looking up country code via IP address! :-)

I assume countries is a rather small table so there should be no problem
adding indexes like:

create index ... on countries (ip_from, ip_to) ...
create index ... on countries (ip_to, ip_from) ...

I imagine a query like:

SELECT a..., c.code
FROM countries c
JOIN addresses a
ON a.ip_address between c.ip_from
and c.ip_to

will perform reasonable well

/Lennart

No, it does not... 100 rows in 13.5s (I'm having the same issue)

Huh, can you put up table definitions and data somewhere so I can
download it and try it out?

/Lennart

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

IP-addr to Country: JOIN question

comp.databases.mysql comp.databases.mysql

Re: IP-addr to Country: JOIN question - 09-16-2010 , 11:40 AM

Re: IP-addr to Country: JOIN question - 09-16-2010 , 11:43 AM