•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 426,591 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 1,667 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser: Programming Forums
Views: 625 | Replies: 3
 |
•
•
Join Date: Jun 2008
Posts: 2
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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:
but it takes too long. Is there a better way?
Here is the structure:
This is similar but different from this: http://forums.mysql.com/read.php?115...747#msg-106747
I've tried several different combinations of indexes, but no success.
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...747#msg-106747
I've tried several different combinations of indexes, but no success.
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;
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;
•
•
Join Date: Apr 2008
Posts: 295
Reputation:
Rep Power: 1
Solved Threads: 41
Posting Whiz in Training
hi
You may try this syntax, what is quite usual in other databases:
SELECT title FROM ranges JOIN ips ON
(
ip BETWEEN start AND stop
)
Question about this create table statement:
Does this table really contains duplicate ip numbers (it wouldn't make any sense)? If not, you should delete ip_id and make ip to be the primary key! (It is really badly bad habit always generating artificial primary keys if tables already have enough columns to form their own natural primary keys from.)
krs,
tesu
You may try this syntax, what is quite usual in other databases:
SELECT title FROM ranges JOIN ips ON
(
ip BETWEEN start AND stop
)
Question about this create table statement:
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;
Does this table really contains duplicate ip numbers (it wouldn't make any sense)? If not, you should delete ip_id and make ip to be the primary key! (It is really badly bad habit always generating artificial primary keys if tables already have enough columns to form their own natural primary keys from.)
krs,
tesu
•
•
Join Date: Jun 2008
Posts: 2
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Hi tesuji, thanks for the reply.
Yes, the ips table contains duplicate IP addresses, so I can't make it a primary key.
Yes, the ips table contains duplicate IP addresses, so I can't make it a primary key.
•
•
Join Date: Apr 2008
Posts: 295
Reputation:
Rep Power: 1
Solved Threads: 41
Posting Whiz in Training
Hi tmarket
Did you already tried this sort of inner join:
select r.title from ranges r, ips s
where r.start <= s.ip and sr.ip <= r.stop
Additionally, you should put indexes on s.ip and (r.start, r.stop), e.g.
CREATE INDEX uniqueIndexName on ranges(start, stop), both indexes in ascending order.
krs,
tesu
p.s. As you may know, your sort of inner join is a so called theta-join, and this sort of joins is well known to be the slowest join one can create. (And you are using two of them)
Did you already tried this sort of inner join:
select r.title from ranges r, ips s
where r.start <= s.ip and sr.ip <= r.stop
Additionally, you should put indexes on s.ip and (r.start, r.stop), e.g.
CREATE INDEX uniqueIndexName on ranges(start, stop), both indexes in ascending order.
krs,
tesu
p.s. As you may know, your sort of inner join is a so called theta-join, and this sort of joins is well known to be the slowest join one can create. (And you are using two of them)
Last edited by tesuji : Jun 23rd, 2008 at 6:20 pm.
|
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
Other Threads in the MySQL Forum
- Previous Thread: return result set in SPs
- Next Thread: Mysql Connection Problem
