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 <wro...@gmail.com> > 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=vegiv...@tuxera.be > > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
