MySQL Forums
Forum List  »  Performance

Optimize IP Range Join
Posted by: T Mark
Date: June 22, 2008 12:07PM

I have 2 tables: an IP Address table, and an IP Ranges table. I want to retrieve a list of ranges in the ranges table for which one or more of the IP addresses in the IP address table fall within that range. The IP addresses are represented as integers.

What I'm trying to use is this:

SELECT title FROM ranges JOIN ips ON ip BETWEEN start AND stop

but it takes too long. Is there a better way?

Here is the structure:

mysql> describe ranges;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| range_id   | int(20) unsigned | NO   | PRI | NULL    | auto_increment |
| start      | int(10) unsigned | NO   | UNI | NULL    |                |
| stop       | int(10) unsigned | NO   | UNI | NULL    |                |
| title      | varchar(200)     | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

mysql> describe ips;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| ip_id      | int(20) unsigned | NO   | PRI | NULL    | auto_increment |
| ip         | int(10) unsigned | NO   | MUL | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

This is similar but different from this: http://forums.mysql.com/read.php?115,106747,106747#msg-106747

I've tried several different combinations of indexes, but no success.

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimize IP Range Join
3713
June 22, 2008 12:07PM
1823
June 22, 2008 10:46PM


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.