Thanks for the idea. I have changed the datatype to bigint, the result is not changed.
mysql> desc select * from ip_test where startNum <= 3061579775 and endNum >= 3061579775; +----+-------------+---------+------+-----------------+------+---------+------+--------+-------------+ | 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 | +----+-------------+---------+------+-----------------+------+---------+------+--------+-------------+ CREATE TABLE `ip_test` ( `startNum` bigint(20) NOT NULL, `endNum` bigint(20) NOT 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 | 在 2010年11月9日 下午5:20，Aveek Misra <ave...@yahoo-inc.com> 写道： > I don't see how BETWEEN is not equivalent to (startNum <= and endNum >=). Of > course please try and let us know if that resolves the issue. But if it > doesn't, I suspect it is because the indexes are created on columns which are > floating point data type. That's because floating point numbers are > approximate and not stored as exact values. Attempts to treat double values > as exact in comparison may lead to the kind of issues that you are getting. I > could be wrong though; but if Johan's trick does not work, you might try and > change the data type to DECIMAL to see if it helps (or BIGINT if your numbers > are not using any digits after the decimal since BIGINT and DOUBLE both use 8 > bytes for storage). > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
