List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:July 28 2011 1:27pm
Subject:Re: index problem
View as plain text  
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" <xucheng@stripped>
> To: mysql@stripped
> 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
Thread
index problemxucheng28 Jul
  • Re: index problemJohan De Meersman28 Jul
    • Re: index problemxucheng28 Jul
  • Re: index problemRik Wasmus28 Jul