List:General Discussion« Previous MessageNext Message »
From:Doug Date:July 13 2012 2:02am
Subject:why this query doesn't use index?
View as plain text  
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.
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