List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:November 9 2010 1:22pm
Subject:Re: a query not using index
View as plain text  
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
Thread
a query not using indexwroxdb9 Nov
  • Re: a query not using indexJohan De Meersman9 Nov
    • Re: a query not using indexwroxdb9 Nov
    • Re: a query not using indexAveek Misra9 Nov
      • Re: a query not using indexwroxdb9 Nov
        • Re: a query not using indexAveek Misra9 Nov
          • Re: a query not using indexJohnny Withers9 Nov
  • Re: a query not using indexMySQL)9 Nov