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).
Thanks Aveek On Nov 9, 2010, at 1:21 PM, Johan De Meersman wrote: > Indexes typically only work on the left-hand-side. Rewrite as > select * from ip_test where startNum <= 3061579775 and endNum >= 3061579775; > > Magic will happen. > > > 2010/11/9 wroxdb <wro...@gmail.com> > >> 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. >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be >> >> > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
