Try this:
ALTER TABLE ipaddr ADD INDEX `ip_search` (id, ip_int_start, ip_int_end);
An explain would then yield:
ysql> EXPLAIN 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 | index | ip_info_active | ip_search | 12 |
NULL | 5 | Using where; Using index |
+----+-------------+--------+-------+----------------+-----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
The key difference is it now says 'Using index'. Although it had previously
identified a matching key, MySQL relies on your query and index having a
matching set of columns. So if you're selecting the ID column, and ranging
from the start and end columns, the index needs to have all three of those
columns in it.
Using a different type of index may or may not help, I can't really offer
much on that, but most of the time the fun is getting the correct columns to
trigger the index. Grouping and/or sorting may also present problems,
especially on large tables, so watching the Extra column in the explain
output can be really helpful to figure out what the engine is actually doing
with the query.
Mike
On Mon, Mar 29, 2010 at 6:30 PM, Jennifer Wollesen <[email protected]>wrote:
> 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
>
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
