Probably indexes need to be rebuilt using myisamchk after you changed the data type of the index columns. Apart from that I can't see why your query is not using the indexes. Is it possible that the cardinality of the column values is so low that indexes are not being used? You could try and run a ANALYZE TABLE (or myismachk -a for MyISAM tables) and then a "SHOW INDEX" to see the cardinality information for these key columns.
Thanks Aveek On Nov 9, 2010, at 3:43 PM, wroxdb wrote: > 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 <[email protected]> 写道： >> 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/[email protected]
