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
