The optimizer is right, you are wrong, as simple as that :-) <value> between [field1] and [field2] cannot use indices, as your primary reference is a constant, not a field. Rewrite that to "start >= 1988778880 and end <= 1988778880" and the optimizer should pick up the index.
Index hints are rarely ever needed. It's best to stay away from them unless you know exactly what's going on under the hood :-) ----- Original Message ----- > From: "xucheng" <xuch...@sankuai.com> > To: mysql@lists.mysql.com > Sent: Thursday, 28 July, 2011 2:50:46 PM > Subject: index problem > > Hi > i found a strange problem . when i using index for 'select' , i > got a slower result than without index . > i have a tabe : > create table geo_query ( > `id` int(10) unsigned not null auto_increment , > `start` bigint(20) unsigned not null , > `end` bigint(20) unsigned not null, > `desc` varchar(1000) not null, > primary key (`id`) , > key `range` (`start`,`end`) > ) engine=myisam ; > the whole table contains 430000 rows . > > 1, the query ' select * from geo_query where 1988778880 between > start and end ;' used 0.15 second ; > and i used 'explain' and found that it didn't use index and > scanned the whole table . > 2, so i changed the query for ' select * from geo_query force > index(`range`) where 1988778880 between start and end ;' . it used > 0.36 second . > i can't figure it out .why the query used index spend more time > than not ? > any comment appreciate : ) > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
