ip2location never uses index.

I've been looking for a solution and I figured somebody here aught to know.

[killroy]

I have a standard IP to location database with the basic fields:

ipfrom INT(10) UNSIGNED NOT NULL
,ipto INT(10) UNSIGNED NOT NULL
,countryshort CHAR(2)
,countrylong VARCHAR(255)

Now I've tried any combination of key on the ipto and ipfrom fields like
,PRIMARY KEY (ipfrom,ipto)
,UNIQUE ipfrom(ipfrom)
,UNIQUE ipto(ipto)

and query syntax like:

WHERE ip BETWEEN ipfrom AND ipto
or
WHERE ip>=ipfrom AND ip<=ipto

but I can never get it to use any of the indexes.

the best I've managed so far on a describe is:

table: ip2country
type: ALL
possible_keys: PRIMARY,ipto,ipfrom
key: NULL
key_len: NULL
ref: NULL
rows: 56968
Extra: range checked for each record (index map: 7)

I simply cannot get it to use the index. And I need it to do a query into my log database so I can get the IPs with the country code next to them.

Have any of you gotten the ip database in this style to work? Is there a different way of writing the ranges that will work?

Please, I'm desperate!

SN

[killroy]

Have any of you done ANY country resolution in logs? Doing it for one ip is fine without index, but if you do it for 100s or 100s of hits per day I need to use the index. I simply do not understand why it doesn't use it, and all the documentation indicates that it should.

SN

[Slade]

Have you tried breaking the IPs into pieces?

Something like:

ipfrom -> ipfrom1, ipfrom2, ipfrom3, ipfrom4
192.168.0.1 -> 192, 168, 0, 1

I don't know whether it would then make any sense to break down the ipto or not.

PS(please sticky) Where'd you get your IP->country list?

[killroy]

All IPs are stored as single integers, no parts. should be a simple int to int compare.

Jsut google for ip2country or ip2location and you'll find dozends.

I was hoping to find somebody who is using it. Country resolution is not exactly an obscure technique, come on some of you must be using this successfully.

SN

[killroy]

Ok, I see I have to give a bit more detail for somebody to help me out ;)

This is the table definition:

CREATE TABLE 'ip2country' ( 
 'ipfrom' int(10) unsigned NOT NULL default '0', 
 'ipto' int(10) unsigned NOT NULL default '0', 
 'countryshort' char(2) default NULL, 
 'countrylong' varchar(255) default NULL, 
 PRIMARY KEY ('ipfrom','ipto'), 
 UNIQUE KEY 'ipto' ('ipto'), 
 UNIQUE KEY 'ipfrom' ('ipfrom'), 
 KEY 'countryshort' ('countryshort') 
)TYPE=MyISAM 

This table contains data like this:

+----------+----------+------------+--------------+ 
¦ ipfrom  ¦ ipto   ¦countryshort¦  countrylong¦ 
+----------+----------+------------+--------------+ 
¦    0 ¦ 33996343 ¦ US     ¦UNITED STATES ¦ 
¦ 33996344 ¦ 33996351 ¦ UK     ¦UNITED KINGDOM¦ 
¦ 33996352 ¦ 68259583 ¦ US     ¦UNITED STATES ¦ 
¦ 68259584 ¦ 68259599 ¦ CA     ¦CANADA    ¦ 
+----------+----------+------------+--------------+ 

Each IP is stored in numerical format.

In THEORY, I can do a query like:

SELECT countrylong 
FROM ip2country 
WHERE inet_aton("218.185.127.81") BETWEEN ipfrom AND ipto; 

and the result is as expected:

+-----------+ 
¦countrylong¦ 
+-----------+ 
¦ AUSTRALIA ¦ 
+-----------+ 

The problem is that, as a quick DESCRIBE shows:

+----------+----+-------------+----+-------+----+-----+----------+ 
¦table   ¦type¦possible_keys¦key ¦key_len¦ref ¦rows ¦Extra   ¦ 
+----------+----+-------------+----+-------+----+-----+----------+ 
¦ip2country¦ALL ¦NULL     ¦NULL¦ NULL ¦NULL¦56968¦where used¦ 
+----------+----+-------------+----+-------+----+-----+----------+ 

It does NOT use the indices as all.

This of course means, I can do a single IP lookup no problem. But I'll never do high speed lookups for a query on a log table wit ha few 100 rows t oadd a country column.

what am I doing wrong?

As mentioned previously, I've tried different syntax besides BETWEEN such as >= and the like, same results. only if I do a straight = (equality) to one of the range edges does it use the index. I need it to use ranges.

Thanks again for your help.

Anybody who can figure it out get's my ip2country table (freely available I might add) :)

SN

[mark_roach]

If you have all the ranges covered in your table you can avoid using the ipto column in your query.

You could firstly try :

select max(ipfrom)
from ip2country
where ipfrom <= ip;

This would return a single row and I would expect it to use an index.

You then use the value from the above query in:

select countrylong
from ip2county
where ipfrom = value;

Which should again should use an index.

If you don't have all the ranges covered you will need to check that the ipto value of the row returned above is not less than your given ip.

For a large table you may find that the two queries (if they do use your indexes) will out perform your single query.

[killroy]

The problem is I can't join those into another log extraction query.

The idea is that I take another yquery that extracts some features form teh logs, and join it to the ip2country table to return the country withe each ip.

But your idea is interesting, as no ipfrom can be larger then another ipfrom AND smaller then the respective ipto. so theoretically I jsut need one comparison to one of the values.

I'll try working along those lines.

The strange thing is though that ALL samples and documentation use those two values in a comparison query, and as far as I understand mysql that should use the index.

SN