It could be that the optimizer thinks a table scan will end up accessing less
rows than using the index.
Are the IP ranges mutually exclusive? If so, you might try adding a LIMIT 1.
That should cause it to favor the index.
Jenn
On Mar 29, 2010, at 4:47 PM, Eric Goebel wrote:
> :( I just clicked the send button too fast:
>
> select id from ipaddr where inet_aton('192.168.1.1') between
> ip_int_start and ip_int_end
> +----+-------------+---------+------+----------------+------+---------+------+---------+-------------+
> | id | select_type | table | type | possible_keys | key | key_len
> | ref | rows | Extra |
> +----+-------------+---------+------+----------------+------+---------+------+---------+-------------+
> | 1 | SIMPLE | ipaddr | ALL | ip_info_active | NULL | NULL |
> NULL | 2000000 | Using where |
> +----+-------------+---------+------+----------------+------+---------+------+---------+-------------+
>
> if I change the size of the ip address it will sometimes use the index:
> explain select id from ipaddr where 32222222222 < ip_int_start;
> +----+-------------+---------+-------+----------------+----------------+---------+------+------+-------------+
> | id | select_type | table | type | possible_keys | key
> | key_len | ref | rows | Extra |
> +----+-------------+---------+-------+----------------+----------------+---------+------+------+-------------+
> | 1 | SIMPLE | ipaddr | range | ip_info_active | ip_info_active
> | 4 | NULL | 4 | Using where |
> +----+-------------+---------+-------+----------------+----------------+---------+------+------+-------------+
>
> explain select blockid from ipaddr where 32222222 < ip_int_start;
> +----+-------------+---------+------+----------------+------+---------+------+---------+-------------+
> | id | select_type | table | type | possible_keys | key | key_len
> | ref | rows | Extra |
> +----+-------------+---------+------+----------------+------+---------+------+---------+-------------+
> | 1 | SIMPLE | ipaddr | ALL | ip_info_active | NULL | NULL |
> NULL | 2000000 | Using where |
> +----+-------------+---------+------+----------------+------+---------+------+---------+-------------+
>
> thebigdog,
> I haven't tried any other type of index except btree.
>
> Thanks,
> Eric
>
> On Mon, Mar 29, 2010 at 4:37 PM, Eric Goebel <[email protected]> wrote:
>> explain select blockid from ipblock where 32222222 < ip_int_start;
>> +----+-------------+---------+------+----------------+------+---------+------+---------+-------------+
>> | id | select_type | table | type | possible_keys | key | key_len
>> | ref | rows | Extra |
>> +----+-------------+---------+------+----------------+------+---------+------+---------+-------------+
>> | 1 | SIMPLE | ipblock | ALL | ip_info_active | NULL | NULL
>> | NULL | 1732405 | Using where |
>> +----+-------------+---------+------+----------------+------+---------+------+---------+-------------+
>>
>>
>> On Mon, Mar 29, 2010 at 4:20 PM, Joseph Scott <[email protected]> wrote:
>>> On Mon, Mar 29, 2010 at 4:13 PM, Eric Goebel <[email protected]> wrote:
>>>> 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?
>>>
>>>
>>> Sounds like you've run EXPLAIN on this query then. What does the
>>> EXPLAIN output look like?
>>>
>>>
>>> --
>>> Joseph Scott
>>> [email protected]
>>> http://josephscott.org/
>>>
>>
>
> _______________________________________________
>
> UPHPU mailing list
> [email protected]
> http://uphpu.org/mailman/listinfo/uphpu
> IRC: #uphpu on irc.freenode.net
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
