On 3/29/10 4:13 PM, Eric Goebel wrote:
> So I have a myisam table that contains around 2,000,000 ranges of IP address:
>
> create table ipaddr {
> id int(10) unsigned not null,
> ip_int_start int(10) unsigned not null,
> ip_int_end int(10) unsigned not null,
> PRIMARY KEY (`id`),
> KEY `ip_info_active` (`ip_int_start`,`ip_int_end`)
> }
>
> I run this query on the data set fairly often:
>
> select id from ipaddr where inet_aton('192.168.1.1') between
> ip_int_start and ip_int_end
>
> Depending on the ip address, the query can take between 2 and 10
> seconds to run with a hefty load on the server.
>
> I've found that the between operation doesn't use the index very well.
> Is there another type of index I could use or a better way to optimize
> this data for a faster lookup?
What type of indexes have you tried or used already? Can you provide the index
ddl as well for the ones you have tried?
Thanks,
--
thebigdog
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
