Optimize IP Range Join

tmarket · #1 23-Jun-2008, 10:55

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, and the IP ranges are non-overlapping.

What I'm trying to use is this:

Code:

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:

Code:

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: forums.mysql.com

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

Code:

CREATE TABLE `ranges` (
  `range_id` int(20) unsigned NOT NULL auto_increment,
  `start` int(10) unsigned NOT NULL,
  `stop` int(10) unsigned NOT NULL,
  `title` varchar(200) NOT NULL,
  PRIMARY KEY  (`range_id`),
  UNIQUE KEY (`start`),
  UNIQUE KEY (`stop`),
  KEY `title` (`title`)
) ENGINE=MyISAM;

Code:

CREATE TABLE `ips` (
  `ip_id` int(20) unsigned NOT NULL auto_increment,
  `ip` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`ip_id`),
  KEY `ip` (`ip`)
) ENGINE=MyISAM;

TurboPT · #2 23-Jun-2008, 15:12

Just a couple of 'formality' questions...

1. what version of MySQL do you have?
2. how do you define "takes too long"?

3. Are you getting some strange Cartesian product from the tables, perhaps?
I'm not clear on how the JOIN with 'ip' could be working (unless that is general form or something), as there is not an 'ip' field in the ranges table.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Help with MySQL join	Blake	MySQL / PHP Forum	5	13-Feb-2007 08:02
Hard drive/CPU Diagnoses Issues	binarybug	Computer Hardware Forum	1	22-Jan-2007 19:23
earn money google directory	limkockchuan	Advertising & Affiliates Forum	0	26-Nov-2006 04:29
Need help generating a random number	Allie	C Programming Language	7	09-Nov-2005 22:18

Optimize IP Range Join

Re: Optimize IP Range Join