Hi Willem:
I have the same database and the only thing different is that I included
the country iso name into the primary key:
mysql> describe ip_country_database;
+---------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| ipstart | int(10) unsigned | | PRI | 0 | |
| ipend | int(10) unsigned | | PRI | 0 | |
| iso | char(2) | | PRI | | |
+---------+------------------+------+-----+---------+-------+
And my system indeed uses the indexes:
mysql> explain SELECT * FROM ip_country_database WHERE 12456789 BETWEEN
ipstart AND ipend;
+---------------------+-------+---------------+---------+---------+------+--
-----+--------------------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+---------------------+-------+---------------+---------+---------+------+--
-----+--------------------------+
| ip_country_database | index | NULL | PRIMARY | 10 | NULL |
42971 | Using where; Using index |
+---------------------+-------+---------------+---------+---------+------+--
-----+--------------------------+
System: MySQL server 4.0.13 on a Red Hat 7.2 box.
Cheers,
Jose Miguel.
----- Original Message -----
From: "Willem Bison" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, October 05, 2003 1:08 AM
Subject: ip range lookup
> I have a table that maps ip-ranges to countries: each record consists of 2
> ip numbers (unsigned int's) and the country in which all ip's between
those
> two are located.
> How should I setup the table to have fast ip lookups ?
> Making a primary key of the 2 ip's and doing a 'select .. between ip1 and
> ip2' doesn't use the index:
>
> mysql> explain select * from ipcountry where 123456789 between ip1 and
ip2;
>
+-----------+------+---------------+------+---------+------+-------+--------
> -----+
> | table | type | possible_keys | key | key_len | ref | rows | Extra
> |
>
+-----------+------+---------------+------+---------+------+-------+--------
> -----+
> | ipcountry | ALL | NULL | NULL | NULL | NULL | 58229 | Using
> where |
>
+-----------+------+---------------+------+---------+------+-------+--------
> -----+
>
> Should I add a helper column - f.e. a column that contains the 'leftmost'
> byte of the ip - and index that ?
>
> Thanks,
> Willem Bison
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
