List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:November 9 2010 1:22pm
Subject:Re: a query not using index
View as plain text  
On 11/8/2010 10:47 PM, wroxdb wrote:
> 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.
> 

Have you tried a combined index of (startnum,endnum) instead of two
single-column indexes?

You may still run into problems, though, because ranged searches are
usually performed as

WHERE column_A BETWEEN X AND Y

and not as

WHERE X BETWEEN column_A and column_B

and the optimizer has been designed to evaluate the first pattern but
not the second.

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
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