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
