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
