List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:September 7 2005 7:14am
Subject:Re: trouble understanding why query is not using the index.
View as plain text  
Hello.


>>> so why does 'explain select * from files where
>>> (pathref,version)=(129286,0);' scan the whole table?

It is documented that this syntax can't be optimized yet. See:
  http://dev.mysql.com/doc/mysql/en/row-subqueries.html



Jason Pyeron <jpyeron@stripped> wrote:
> On Tue, 6 Sep 2005, Dan Nelson wrote:
> 
>> In the last episode (Sep 06), Jason Pyeron said:
>>> there is an unique key index 'pathref_2 (pathref,version)' on this
>>> table.
>>>
>>> so why does 'explain select * from files where
>>> (pathref,version)=(129286,0);' scan the whole table?
>>
>> I have to admit I have never seen this syntax used in a where clause
>> before.
> 
> never thought not to use it, SQL servers are just big set processing 
> engines.
> 
>> Does "where pathref=129286 and version=0" optimize any better?
> 
> yes, it scans only one row.
> 
>> A quick test of mysql-5.0.11 indicates that it doesn't use indexes at
>> all with the (field1,field2,..)=(value1,value2,..) syntax.  Feel free
>> to file a bug :)
> 
> http://bugs.mysql.com/13024
> 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /    Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com



Thread
trouble understanding why query is not using the index.Jason Pyeron7 Sep
  • Re: trouble understanding why query is not using the index.Dan Nelson7 Sep
    • Re: trouble understanding why query is not using the index.Jason Pyeron7 Sep
      • Re: trouble understanding why query is not using the index.Gleb Paharenko7 Sep
        • Re: trouble understanding why query is not using the index.Jason Pyeron7 Sep
          • Re: trouble understanding why query is not using the index.Gleb Paharenko8 Sep