List:General Discussion« Previous MessageNext Message »
From:wroxdb Date:November 9 2010 3:47am
Subject:a query not using index
View as plain text  
Hello,

I have a query below:

mysql> select * from ip_test where 3061579775 between startNum and endNum;
+------------+------------+---------+----------+------+--------+
| startNum   | endNum     | country | province | city | isp    |
+------------+------------+---------+----------+------+--------+
| 3061514240 | 3061579775 | 中国    | 河南     |      | 联通   |
+------------+------------+---------+----------+------+--------+


the "desc" shows it isn't using the index:

mysql> desc select * from ip_test where 3061579775 between startNum and endNum;
+----+-------------+---------+------+-----------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys   | key  | key_len
| ref  | rows   | Extra       |
+----+-------------+---------+------+-----------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | ip_test | ALL  | startNum,endNum | NULL | NULL
| NULL | 396528 | Using where |
+----+-------------+---------+------+-----------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)


the table structure is:

CREATE TABLE `ip_test` (
  `startNum` double(20,0) default NULL,
  `endNum` double(20,0) default NULL,
  `country` varchar(50) NOT NULL default '',
  `province` varchar(50) NOT NULL default '',
  `city` varchar(50) NOT NULL default '',
  `isp` varchar(100) default NULL,
  KEY `startNum` (`startNum`),
  KEY `endNum` (`endNum`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8



please help, thanks in advance.
Thread
a query not using indexwroxdb9 Nov
  • Re: a query not using indexJohan De Meersman9 Nov
    • Re: a query not using indexwroxdb9 Nov
    • Re: a query not using indexAveek Misra9 Nov
      • Re: a query not using indexwroxdb9 Nov
        • Re: a query not using indexAveek Misra9 Nov
          • Re: a query not using indexJohnny Withers9 Nov
  • Re: a query not using indexMySQL)9 Nov