List:General Discussion« Previous MessageNext Message »
From:Aveek Misra Date:November 9 2010 10:39am
Subject:Re: a query not using index
View as plain text  
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).
>> 

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