IP-addr to Country: JOIN question

#1

Hi folks.

I've become a little stuck trying to work out how to JOIN two tables in what
is maybe a rather unconventional way... For the following, assume all IP
addresses are represented as integers, e.g. '127.0.0.1' would be 2130706433.

Let's say I have a table called 'addresses' containing an IP address column,
'ip', for which I'd like to establish the country code. I also have a
'countries' table containing IP address ranges and their associated country
codes; columns: 'ip_from', 'ip_to', 'code', an example entry from which
might look like:

16777216, 17367039, AUS

As a minimum, the 'countries.ip_to' column is indexed. (As you'll see in a
moment, in the country-code lookup I've been fiddling with, I only use
'ip_to', so other indexes aren't required.)

The quickest way I've come up with of looking up a single country code from
a literal IP address looks like this:

SELECT code
FROM countries
WHERE (ip-to-lookup) <= ip_to
ORDER BY ip_to
LIMIT 1

This seems to work out much quicker than doing a range-check using both
'ip_to' and 'ip_from' columns. I assume the reason for this is because MySQL
only uses one index per query (or so I recall reading somewhere once).

So, I have a way of looking up a specific IP's country code fairly
efficiently.

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! :-)

A.

#2

Andrew C. wrote:

Quote:

Hi folks.

I've become a little stuck trying to work out how to JOIN two tables in what
is maybe a rather unconventional way... For the following, assume all IP
addresses are represented as integers, e.g. '127.0.0.1' would be 2130706433.

Is that wise? I'm no network specialist, but I live in Switzerland and
have email addresses on servers in the USA, Switzerland and one
forwarding address in another country. Today, a physical location
seldom has anything to do with an IP address which might be associated
with it.

Quote:

Let's say I have a table called 'addresses' containing an IP address column,
'ip', for which I'd like to establish the country code. I also have a
'countries' table containing IP address ranges and their associated country
codes; columns: 'ip_from', 'ip_to', 'code', an example entry from which
might look like:

16777216, 17367039, AUS

As a minimum, the 'countries.ip_to' column is indexed. (As you'll see in a
moment, in the country-code lookup I've been fiddling with, I only use
'ip_to', so other indexes aren't required.)

The quickest way I've come up with of looking up a single country code from
a literal IP address looks like this:

SELECT code
FROM countries
WHERE (ip-to-lookup) <= ip_to
ORDER BY ip_to
LIMIT 1

This seems to work out much quicker than doing a range-check using both
'ip_to' and 'ip_from' columns. I assume the reason for this is because MySQL
only uses one index per query (or so I recall reading somewhere once).
So, I have a way of looking up a specific IP's country code fairly
efficiently.

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.

The easiest way probably would be to wrap your lookup query into a
stored function which could be used in a view.

Quote:

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! :-)

A.

#3

Op 30-08-10 12:21, Andrew C. schreef:

Quote:

Hi folks.

I've become a little stuck trying to work out how to JOIN two tables in what
is maybe a rather unconventional way... For the following, assume all IP
addresses are represented as integers, e.g. '127.0.0.1' would be 2130706433.

Let's say I have a table called 'addresses' containing an IP address column,
'ip', for which I'd like to establish the country code. I also have a
'countries' table containing IP address ranges and their associated country
codes; columns: 'ip_from', 'ip_to', 'code', an example entry from which
might look like:

16777216, 17367039, AUS

As a minimum, the 'countries.ip_to' column is indexed. (As you'll see in a
moment, in the country-code lookup I've been fiddling with, I only use
'ip_to', so other indexes aren't required.)

The quickest way I've come up with of looking up a single country code from
a literal IP address looks like this:

SELECT code
FROM countries
WHERE (ip-to-lookup)<= ip_to
ORDER BY ip_to
LIMIT 1

This seems to work out much quicker than doing a range-check using both
'ip_to' and 'ip_from' columns. I assume the reason for this is because MySQL
only uses one index per query (or so I recall reading somewhere once).

So, I have a way of looking up a specific IP's country code fairly
efficiently.

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! :-)

A.

dependendent subquery:

SELECT ip-to-lookup,
(SELECT code
FROM countries
WHERE (ip-to-lookup)<= ip_to
ORDER BY ip_to
LIMIT 1)
FROM table-with-ip-addresses

--
Luuk

#4

Quote:

I've become a little stuck trying to work out how to JOIN two tables in what
is maybe a rather unconventional way... For the following, assume all IP
addresses are represented as integers, e.g. '127.0.0.1' would be 2130706433.

Is that wise? I'm no network specialist, but I live in Switzerland and
have email addresses on servers in the USA, Switzerland and one
forwarding address in another country. Today, a physical location
seldom has anything to do with an IP address which might be associated
with it.

IP addresses are often allocated from a block controlled by the ISP
you are connected to, and since the connection between you and your
ISP is usually short (e.g. not an international call), this sort
of data can actually work a fair percentage of the time. "portable"
IP blocks and the possibility of a private link between the same
company in different countries can screw this up.

The existence of dial-up phone lines, international dialing, and
satellite phones ensures that the data will never be completely
accurate. So is the issue that a given computer can change country
without much notice, and without moving. And then there's wireless
phones/computers, which at least can be a few miles on the other
side of the border.

This method of associating IP with country is not too bad for
something like choosing a default language (which the user can
change). It's not such a good thing for enforcing export laws
(proxies are one way around this; you'll see the IP address and
country of the proxy).

Email addresses are less tied to geography since you don't need to
have a *direct* connection to the server handling your email.

Quote:

Let's say I have a table called 'addresses' containing an IP address column,
'ip', for which I'd like to establish the country code. I also have a
'countries' table containing IP address ranges and their associated country
codes; columns: 'ip_from', 'ip_to', 'code', an example entry from which
might look like:

16777216, 17367039, AUS

Mysql has functions to convert between integers and conventional dotted
notation, so you can store them as integers but display as they are
normally presented.

Quote:

As a minimum, the 'countries.ip_to' column is indexed. (As you'll see in a
moment, in the country-code lookup I've been fiddling with, I only use
'ip_to', so other indexes aren't required.)

The quickest way I've come up with of looking up a single country code from
a literal IP address looks like this:

SELECT code
FROM countries
WHERE (ip-to-lookup) <= ip_to
ORDER BY ip_to
LIMIT 1

Why not:
SELECT code
FROM countries
WHERE (ip-to-lookup) <= ip_to AND (ip-to-lookup) >= ip_from

This should produce 0 or 1 records. Does your data fill in records
for all of the IP blocks that don't have a country (yet)? Localhost,
multicast, reserved private addresses, and as-yet-unallocated blocks
aren't associated with countries.

If there are any IP addresses in which the second query produces 0
results, the first query may be producing a wrong result.

Quote:

This seems to work out much quicker than doing a range-check using both
'ip_to' and 'ip_from' columns. I assume the reason for this is because MySQL
only uses one index per query (or so I recall reading somewhere once).

This doesn't make a lot of sense. Assuming the ip_to index gets
used, a lookup on 255.255.255.255 is going to return ALL of the
data for your query (and have to sort it, although it does have
the index). The dual-range query doesn't have to return all of the
data, but it does have to disqualify all but one of the records.
That seems like a wash between the two (both have to fetch all of
the records), except for all the network time to return the result.

Quote:

So, I have a way of looking up a specific IP's country code fairly
efficiently.

And only fairly accurately.

Quote:

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.

The dual-range query is easier to join.

#5

"Gordon Burditt" <gordonb.fr3nq (AT) burditt (DOT) org> wrote

Quote:

I've become a little stuck trying to work out how to JOIN two tables in
what
is maybe a rather unconventional way... For the following, assume all IP
addresses are represented as integers, e.g. '127.0.0.1' would be
2130706433.

Is that wise? I'm no network specialist, but I live in Switzerland and
have email addresses on servers in the USA, Switzerland and one
forwarding address in another country. Today, a physical location
seldom has anything to do with an IP address which might be associated
with it.

IP addresses are often allocated from a block controlled by the ISP
you are connected to, and since the connection between you and your
ISP is usually short (e.g. not an international call), this sort
of data can actually work a fair percentage of the time. "portable"
IP blocks and the possibility of a private link between the same
company in different countries can screw this up.

The existence of dial-up phone lines, international dialing, and
satellite phones ensures that the data will never be completely
accurate. So is the issue that a given computer can change country
without much notice, and without moving. And then there's wireless
phones/computers, which at least can be a few miles on the other
side of the border.

This method of associating IP with country is not too bad for
something like choosing a default language (which the user can
change). It's not such a good thing for enforcing export laws
(proxies are one way around this; you'll see the IP address and
country of the proxy).

Email addresses are less tied to geography since you don't need to
have a *direct* connection to the server handling your email.

Let's say I have a table called 'addresses' containing an IP address
column,
'ip', for which I'd like to establish the country code. I also have a
'countries' table containing IP address ranges and their associated
country
codes; columns: 'ip_from', 'ip_to', 'code', an example entry from which
might look like:

16777216, 17367039, AUS

Mysql has functions to convert between integers and conventional dotted
notation, so you can store them as integers but display as they are
normally presented.

As a minimum, the 'countries.ip_to' column is indexed. (As you'll see in
a
moment, in the country-code lookup I've been fiddling with, I only use
'ip_to', so other indexes aren't required.)

The quickest way I've come up with of looking up a single country code
from
a literal IP address looks like this:

SELECT code
FROM countries
WHERE (ip-to-lookup) <= ip_to
ORDER BY ip_to
LIMIT 1

Why not:
SELECT code
FROM countries
WHERE (ip-to-lookup) <= ip_to AND (ip-to-lookup) >= ip_from

This should produce 0 or 1 records. Does your data fill in records
for all of the IP blocks that don't have a country (yet)? Localhost,
multicast, reserved private addresses, and as-yet-unallocated blocks
aren't associated with countries.

If there are any IP addresses in which the second query produces 0
results, the first query may be producing a wrong result.

I accept that the 'countries' table needs to have a comprehensive collection
of IP-address ranges, but that appears to be the case for the table I have.

Quote:

This seems to work out much quicker than doing a range-check using both
'ip_to' and 'ip_from' columns. I assume the reason for this is because
MySQL
only uses one index per query (or so I recall reading somewhere once).

This doesn't make a lot of sense. Assuming the ip_to index gets
used, a lookup on 255.255.255.255 is going to return ALL of the
data for your query (and have to sort it, although it does have
the index). The dual-range query doesn't have to return all of the
data, but it does have to disqualify all but one of the records.
That seems like a wash between the two (both have to fetch all of
the records), except for all the network time to return the result.

As I explained, MySQL appears to use only a single index per query, so, when
range-checking a value between two columns. This makes the two-column query
slow.

It may seem unlikely to you that the one-column solution I provided is
quicker (actually much quicker), but it is.

Quote:

So, I have a way of looking up a specific IP's country code fairly
efficiently.

And only fairly accurately.

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.

The dual-range query is easier to join.

Thank you for taking the time to reply, but (for reasons I highlighted
originally), I've already abandoned the idea of the two-column range check.
It's too slow.

A.

#6

"Luuk" <Luuk (AT) invalid (DOT) lan> wrote

Quote:

Op 30-08-10 12:21, Andrew C. schreef:
Hi folks.

I've become a little stuck trying to work out how to JOIN two tables in
what
is maybe a rather unconventional way... For the following, assume all IP
addresses are represented as integers, e.g. '127.0.0.1' would be
2130706433.

Let's say I have a table called 'addresses' containing an IP address
column,
'ip', for which I'd like to establish the country code. I also have a
'countries' table containing IP address ranges and their associated
country
codes; columns: 'ip_from', 'ip_to', 'code', an example entry from which
might look like:

16777216, 17367039, AUS

As a minimum, the 'countries.ip_to' column is indexed. (As you'll see in
a
moment, in the country-code lookup I've been fiddling with, I only use
'ip_to', so other indexes aren't required.)

The quickest way I've come up with of looking up a single country code
from
a literal IP address looks like this:

SELECT code
FROM countries
WHERE (ip-to-lookup)<= ip_to
ORDER BY ip_to
LIMIT 1

This seems to work out much quicker than doing a range-check using both
'ip_to' and 'ip_from' columns. I assume the reason for this is because
MySQL
only uses one index per query (or so I recall reading somewhere once).

So, I have a way of looking up a specific IP's country code fairly
efficiently.

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! :-)

A.

dependendent subquery:

SELECT ip-to-lookup,
(SELECT code
FROM countries
WHERE (ip-to-lookup)<= ip_to
ORDER BY ip_to
LIMIT 1)
FROM table-with-ip-addresses

Thanks very much for the reply. It's one for me to jot down for future
reference! (Just out of interest, I wonder if there is a JOIN-type syntax
for the same thing? I suppose I'm just curious to see if there is an
explicit JOIN way of doing it.)

Your query appears to do the trick, although it's highlighted that even the
one-column look-up we started with (although much faster than the two-column
range-check) is still quite slow.

The above query gets through about 700 IP addresses per minute, which isn't
breathtaking! ;-)

Through no fault of yours I hasten to add!

Since yesterday, I've been looking into using a spatial index as per the
following blog article:

http://jcole.us/blog/archives/2007/1...and-mysql-gis/

Using this approach, I seem to be able to perform country code lookups at
more like 100 thousand per minute, although it does seem quite a complex
approach to make something that doesn't seem so unusual work at anything
like reasonable speeds...

A.

#7

On 2010-08-30 12:21, Andrew C. wrote:
[...]

Quote:

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

#8

Op 31-08-10 07:46, Andrew C. schreef:

Quote:

"Luuk"<Luuk (AT) invalid (DOT) lan> wrote in message
news:7h5tk7-f9s.ln1 (AT) qqqqq (DOT) xs4all.nl...
Op 30-08-10 12:21, Andrew C. schreef:
Hi folks.

I've become a little stuck trying to work out how to JOIN two tables in
what
is maybe a rather unconventional way... For the following, assume all IP
addresses are represented as integers, e.g. '127.0.0.1' would be
2130706433.

Let's say I have a table called 'addresses' containing an IP address
column,
'ip', for which I'd like to establish the country code. I also have a
'countries' table containing IP address ranges and their associated
country
codes; columns: 'ip_from', 'ip_to', 'code', an example entry from which
might look like:

16777216, 17367039, AUS

As a minimum, the 'countries.ip_to' column is indexed. (As you'll see in
a
moment, in the country-code lookup I've been fiddling with, I only use
'ip_to', so other indexes aren't required.)

The quickest way I've come up with of looking up a single country code
from
a literal IP address looks like this:

SELECT code
FROM countries
WHERE (ip-to-lookup)<= ip_to
ORDER BY ip_to
LIMIT 1

This seems to work out much quicker than doing a range-check using both
'ip_to' and 'ip_from' columns. I assume the reason for this is because
MySQL
only uses one index per query (or so I recall reading somewhere once).

So, I have a way of looking up a specific IP's country code fairly
efficiently.

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! :-)

A.

dependendent subquery:

SELECT ip-to-lookup,
(SELECT code
FROM countries
WHERE (ip-to-lookup)<= ip_to
ORDER BY ip_to
LIMIT 1)
FROM table-with-ip-addresses

Thanks very much for the reply. It's one for me to jot down for future
reference! (Just out of interest, I wonder if there is a JOIN-type syntax
for the same thing? I suppose I'm just curious to see if there is an
explicit JOIN way of doing it.)

There should be, but i dont have the proper test data to see how
performance is ;-)
(and proper indexes might help here....)

And, i do hope, that an the ranges of ip-addresses are not overlapping....

SELECT
INET_NTOA(ip_to_lookup) as ip ,
code as country
FROM table_with_ip_addresses
LEFT JOIN countries ON (ip_to_lookup)<= ip_to and ip_to_lookup>=ip_from
;

Quote:

Your query appears to do the trick, although it's highlighted that even the
one-column look-up we started with (although much faster than the two-column
range-check) is still quite slow.

The above query gets through about 700 IP addresses per minute, which isn't
breathtaking! ;-)

Through no fault of yours I hasten to add!

Since yesterday, I've been looking into using a spatial index as per the
following blog article:

http://jcole.us/blog/archives/2007/1...and-mysql-gis/

Using this approach, I seem to be able to perform country code lookups at
more like 100 thousand per minute, although it does seem quite a complex
approach to make something that doesn't seem so unusual work at anything
like reasonable speeds...

A.

--
Luuk

#9

On Aug 31, 4:55*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

Quote:

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)

#10

On Aug 31, 4:43*pm, Luuk <L... (AT) invalid (DOT) lan> wrote:

Quote:

Op 31-08-10 07:46, Andrew C. schreef:

"Luuk"<L... (AT) invalid (DOT) lan> *wrote in message
news:7h5tk7-f9s.ln1 (AT) qqqqq (DOT) xs4all.nl...
Op 30-08-10 12:21, Andrew C. schreef:
Hi folks.

I've become a little stuck trying to work out how to JOIN two tables in
what
is maybe a rather unconventional way... For the following, assume allIP
addresses are represented as integers, e.g. '127.0.0.1' would be
2130706433.

Let's say I have a table called 'addresses' containing an IP address
column,
'ip', for which I'd like to establish the country code. I also have a
'countries' table containing IP address ranges and their associated
country
codes; columns: 'ip_from', 'ip_to', 'code', an example entry from which
might look like:

16777216, 17367039, AUS

As a minimum, the 'countries.ip_to' column is indexed. (As you'll seein
a
moment, in the country-code lookup I've been fiddling with, I only use
'ip_to', so other indexes aren't required.)

The quickest way I've come up with of looking up a single country code
from
a literal IP address looks like this:

SELECT code
FROM countries
WHERE (ip-to-lookup)<= ip_to
ORDER BY ip_to
LIMIT 1

This seems to work out much quicker than doing a range-check using both
'ip_to' and 'ip_from' columns. I assume the reason for this is because
MySQL
only uses one index per query (or so I recall reading somewhere once)..

So, I have a way of looking up a specific IP's country code fairly
efficiently.

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 approachto
looking up country code via IP address! :-)

A.

dependendent subquery:

SELECT ip-to-lookup,
(SELECT code
FROM countries
WHERE (ip-to-lookup)<= ip_to
ORDER BY ip_to
LIMIT 1)
FROM table-with-ip-addresses

Thanks very much for the reply. It's one for me to jot down for future
reference! (Just out of interest, I wonder if there is a JOIN-type syntax
for the same thing? I suppose I'm just curious to see if there is an
explicit JOIN way of doing it.)

There should be, but i dont have the proper test data to see how
performance is ;-)
(and proper indexes might help here....)

And, i do hope, that an the ranges of ip-addresses are not overlapping.....

SELECT
* * * * INET_NTOA(ip_to_lookup) as ip ,
* * * * code * *as country
FROM table_with_ip_addresses
LEFT JOIN countries ON (ip_to_lookup)<= ip_to and ip_to_lookup>=ip_from
* * *;

Your query appears to do the trick, although it's highlighted that eventhe
one-column look-up we started with (although much faster than the two-column
range-check) is still quite slow.

The above query gets through about 700 IP addresses per minute, which isn't
breathtaking! ;-)

Through no fault of yours I hasten to add!

Since yesterday, I've been looking into using a spatial index as per the
following blog article:

http://jcole.us/blog/archives/2007/1...-geo-referenci...

Using this approach, I seem to be able to perform country code lookups at
more like 100 thousand per minute, although it does seem quite a complex
approach to make something that doesn't seem so unusual work at anything
like reasonable speeds...

A.

--
Luuk

Ranges do not overlap, correct indexes are in place, I've tried
forcing indexes, restructuring the query 8 different ways, even
building out temp memory tables in a number of different manners... I
can not devise a way to efficiently query for when using a two column
range join... Let alone trying to add a GROUP BY for COUNT(id) ...