RE: why this query doesn't use index?
Things like that are hard to optimize. If you have no overlapping ranges, then this will be much more efficient: http://mysql.rjweb.org/doc.php/latlng > -Original Message- > From: Doug [mailto:d...@hacks.perl.sh] > Sent: Thursday, July 12, 2012 7:03 PM > To: mysql@lists.mysql.com > Subject: why this query doesn't use index? > > Hello, > > can you tell me why my this query doesn't use the index? > > > mysql> explain select * from iploc where 1902800418 between start_ip > and end_ip; > ++-+---+--+---+--+-+--- > ---+---+-+ > | id | select_type | table | type | possible_keys | key | key_len | > ref | rows | Extra | > ++-+---+--+---+--+-+--- > ---+---+-+ > | 1 | SIMPLE | iploc | ALL | start_ip | NULL | NULL| > NULL | 58838 | Using where | > ++-+---+--+---+--+-+--- > ---+---+-+ > 1 row in set (0.00 sec) > > mysql> explain select * from iploc where start_ip <=1902800418 and > end_ip >=1902800418; > ++-+---+--+---+--+-+--- > ---+---+-+ > | id | select_type | table | type | possible_keys | key | key_len | > ref | rows | Extra | > ++-+---+--+---+--+-+--- > ---+---+-+ > | 1 | SIMPLE | iploc | ALL | start_ip | NULL | NULL| > NULL | 58838 | Using where | > ++-+---+--+---+--+-+--- > ---+---+-+ > 1 row in set (0.00 sec) > > > This is the index stru: > > mysql> show index from iploc; > +---++--+--+-+- > --+-+--++--++-+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | > Comment | > +---++--+--+-+- > --+-+--++--++-+ > | iploc | 0 | PRIMARY |1 | id | A > | 58838 | NULL | NULL | | BTREE | | > | iploc | 1 | start_ip |1 | start_ip| A > | 58838 | NULL | NULL | | BTREE | | > | iploc | 1 | start_ip |2 | end_ip | A > | 58838 | NULL | NULL | | BTREE | | > +---++--+--+-+- > --+-+--++--++-+ > 3 rows in set (0.00 sec) > > > Thank you. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql
Re: why this query doesn't use index?
> can you tell me why my this query doesn't use the index? > > > mysql> explain select * from iploc where 1902800418 between start_ip > and end_ip; Hazarding a very quick guess: if this table is what I think it is (NON- overlapping IP ranges + (geo)location), you might want to try: SELECT * FROM iploc WHERE start_ip < 1902800418 ORDER BY start_ip DESC LIMIT 1 ... at least, that is how we solved our geoip-performance problems. The NON- overlappig part is crucial though. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
why this query doesn't use index?
Hello, can you tell me why my this query doesn't use the index? mysql> explain select * from iploc where 1902800418 between start_ip and end_ip; ++-+---+--+---+--+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+---+-+ | 1 | SIMPLE | iploc | ALL | start_ip | NULL | NULL| NULL | 58838 | Using where | ++-+---+--+---+--+-+--+---+-+ 1 row in set (0.00 sec) mysql> explain select * from iploc where start_ip <=1902800418 and end_ip >=1902800418; ++-+---+--+---+--+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+---+-+ | 1 | SIMPLE | iploc | ALL | start_ip | NULL | NULL| NULL | 58838 | Using where | ++-+---+--+---+--+-+--+---+-+ 1 row in set (0.00 sec) This is the index stru: mysql> show index from iploc; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | iploc | 0 | PRIMARY |1 | id | A | 58838 | NULL | NULL | | BTREE | | | iploc | 1 | start_ip |1 | start_ip| A | 58838 | NULL | NULL | | BTREE | | | iploc | 1 | start_ip |2 | end_ip | A | 58838 | NULL | NULL | | BTREE | | +---++--+--+-+---+-+--++--++-+ 3 rows in set (0.00 sec) Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
