thanks . i dropped the primary key , and it still didn't use the index . when i dropped the index `range`, and add two indexes `start` and `end` . it picks up the index , but it still used more seconds than using no index with `start` and `end` indexed as one --`range`.
2011/7/28 Johan De Meersman <vegiv...@tuxera.be>: > 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=helloworldje...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
