List:General Discussion« Previous MessageNext Message »
From:xucheng Date:July 28 2011 2:52pm
Subject:Re: index problem
View as plain text  
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
>
>
Thread
index problemxucheng28 Jul
  • Re: index problemJohan De Meersman28 Jul
    • Re: index problemxucheng28 Jul
  • Re: index problemRik Wasmus28 Jul