I'm struggling to think of an efficient model to describe IPv4 address data. I want to be able to perform a 'whois' type lookup on a dataset within MySQL. Currently I have this:
CREATE TABLE inetnum (
`from_ip` int(11) unsigned NOT NULL,
`to_ip` int(11) unsigned NOT NULL,
`netname` varchar(40) default NULL,
`ip_txt` varchar(60) default NULL,
`descr` varchar(60) default NULL,
`country` varchar(2) default NULL,
`recurse_limit` int(11) NOT NULL default '0',
`unexpected` int(11) NOT NULL default '0',
`rir` enum('APNIC','AFRINIC','ARIN','RIPE','LACNIC') NOT NULL default 'RIPE',
PRIMARY KEY (`from_ip`,`to_ip`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii;
And I want to do queries like this:
SELECT *
FROM inetnum
WHERE INET_ATON('192.168.0.1') BETWEEN from_ip AND to_ip;
But because the upper and lower bounds of the address range are held in different fields, this results in a full table scan:
mysql> EXPLAIN SELECT * FROM `inetnum` WHERE INET_ATON('192.168.0.1') BETWEEN from_ip AND to_ip;
+----+-------------+---------+------+---------------+------+---------+------+---------+------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | inetnum | ALL | NULL | NULL | NULL | NULL | 3800440 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
(and as I'm sure someone will try to point out - not it's not because of the INET_ATON function - using a literal integer makes no difference, nor does using <=to_ip AND >=from_ip).
This is currently running on MySQL 5.0.67. I only have limited scope for changing/upgrading the DBMS.