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