Would a compound index on both startnum and endnum be a better choice? JW
On Tuesday, November 9, 2010, Aveek Misra <ave...@yahoo-inc.com> wrote: > 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 <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=joh...@pixelated.net > > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
