MySQL Forums
Forum List  »  Optimizer & Parser

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
9022
August 02, 2006 07:56AM
4194
August 04, 2006 09:00AM
4054
July 13, 2007 03:30AM
8916
July 18, 2007 04:52PM
3507
December 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.