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 <aveekm@stripped> 写道：
>> 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).
>>
