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 <vegivamp@stripped>:
> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
