GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Optimize IP Range Join

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Optimize IP Range Join

    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-10674 7

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

    Edit: see this post for more information
    http://forum.mysqlperformanceblog.com/s/m/3561/
Working...
X