Skip navigation links

MySQL Forums :: Optimizer & Parser :: How to optimize a range


Advanced Search

How to optimize a range
Posted by: David Spindler ()
Date: August 02, 2006 07:56AM

Hi,
I'm trying to do a join between a table of IP addresses and IP networks. A IP network is basically a range (start-stop) which is guaranteed not to overlap any other network, it can also be represented by the start address and the number of mask bits IE start/maskbits or for example 128.83.135.250/24, in my case all ip addresses are represented by unsigned integers to make math easy.

Is ther anyway to optimize a join between the tables on ip_address between start and stop? I've tried indexing everything I could think of, even trying to use the new geometric objects, and the query is always painfully slow. What it needs is a single B-tree that contains both start and stop, but that's not how mysql indexes work. Any suggestions?



explain select * from ips left join networks on ip_address between start and stop;
+----+-------------+----------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------+
| 1 | SIMPLE | ips | ALL | | | | | 12960 | |
| 1 | SIMPLE | networks | ALL | | | | | 2592 | |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------+




mysql> desc ips;
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| ip_address | int(10) unsigned | NO | PRI | | |
| details | varchar(5) | YES | | | |
+------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc networks;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| net_id | bigint(20) unsigned | NO | PRI | | auto_increment |
| start | int(10) unsigned | NO | UNI | | |
| stop | int(10) unsigned | NO | UNI | | |
| maskbits | int(10) unsigned | NO | | | |
+----------+---------------------+------+-----+---------+----------------+

Options: ReplyQuote


Subject Views Written By Posted
How to optimize a range 7229 David Spindler 08/02/2006 07:56AM
Re: How to optimize a range 3634 Toa Sty 08/04/2006 09:00AM
Re: How to optimize a range 3555 Niko Viitala 07/13/2007 03:30AM
Re: How to optimize a range 7706 Björn Steinbrink 07/18/2007 04:52PM
Re: How to optimize a range 3158 keith cascio 12/13/2008 09:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.