Hi Willem, I don't *think* MySQL optimizes BETWEEN like that to use an index.
Have you tried this?: SELECT * FROM ipcountry WHERE ip1 >= 123456789 AND ip2 <= 123456789; Matt ----- Original Message ----- From: "Willem Bison" Sent: Saturday, October 04, 2003 6:08 PM 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]
