From: Doug Date: July 13 2012 2:02am Subject: why this query doesn't use index? List-Archive: http://lists.mysql.com/mysql/227814 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 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.