:( 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
