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

Thanks
Aveek

On Nov 9, 2010, at 1:21 PM, Johan De Meersman wrote:

> Indexes  typically only work on the left-hand-side. Rewrite as
> select * from ip_test where startNum <= 3061579775 and endNum >= 3061579775;
> 
> Magic will happen.
> 
> 
> 2010/11/9 wroxdb <wroxdb@stripped>
> 
>> Hello,
>> 
>> I have a query below:
>> 
>> mysql> select * from ip_test where 3061579775 between startNum and endNum;
>> +------------+------------+---------+----------+------+--------+
>> | startNum   | endNum     | country | province | city | isp    |
>> +------------+------------+---------+----------+------+--------+
>> | 3061514240 | 3061579775 | 中国    | 河南     |      | 联通   |
>> +------------+------------+---------+----------+------+--------+
>> 
>> 
>> the "desc" shows it isn't using the index:
>> 
>> mysql> desc select * from ip_test where 3061579775 between startNum and
>> endNum;
>> 
>>
> +----+-------------+---------+------+-----------------+------+---------+------+--------+-------------+
>> | 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 |
>> 
>>
> +----+-------------+---------+------+-----------------+------+---------+------+--------+-------------+
>> 1 row in set (0.01 sec)
>> 
>> 
>> the table structure is:
>> 
>> CREATE TABLE `ip_test` (
>> `startNum` double(20,0) default NULL,
>> `endNum` double(20,0) default 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
>> 
>> 
>> 
>> please help, thanks in advance.
>> 
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>> 
>> 
> 
> 
> -- 
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel

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