List:General Discussion« Previous MessageNext Message »
From:Rick James Date:July 16 2012 5:20pm
Subject:RE: why this query doesn't use index?
View as plain text  
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:doug@stripped]
> 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

Thread
why this query doesn't use index?Doug13 Jul
  • Re: why this query doesn't use index?Rik Wasmus13 Jul
  • RE: why this query doesn't use index?Rick James16 Jul