Would a compound index on both startnum and endnum be a better choice?
JW
On Tuesday, November 9, 2010, Aveek Misra <aveekm@stripped> 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 <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).
>>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
--
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped
