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.
