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
[URL=“http://forum.mysqlperformanceblog.com/s/m/3561/”]http://forum.mysqlperformanceblog.com/s/m/3561/[/URL]