Hello.
In my opinion - yes it is, however, trust only the benchmarks :)
Jason Pyeron <jpyeron@stripped> wrote:
>
> Am I correct in the statment second query is faster? This query gets
> executed several million times.
>
> mysql> explain select A.* from files as A where A.pathref=129286 and
> version=(select max(version) from files as B where A.pathref=B.pathref);
>
> +----+--------------------+-------+------+-------------------+-----------+---------+---------------------+------+-------------+
> | id | select_type | table | type | possible_keys | key | key_len |
> ref | rows | Extra |
>
> +----+--------------------+-------+------+-------------------+-----------+---------+---------------------+------+-------------+
> | 1 | PRIMARY | A | ref | pathref_2,pathref | pathref_2 | 4 |
> const | 1 | Using where |
> | 2 | DEPENDENT SUBQUERY | B | ref | pathref_2,pathref | pathref_2 | 4 |
> crisfield.A.pathref | 1 | Using index |
>
> +----+--------------------+-------+------+-------------------+-----------+---------+---------------------+------+-------------+
> 2 rows in set (0.01 sec)
>
> mysql> explain select A.* from files as A where A.pathref=129286 and
> version=(select max(version) from files as B where 129286=B.pathref);
>
> +----+-------------+-------+-------+---------------------------+-----------+---------+-------------+------+------------------------------+
> | id | select_type | table | type | possible_keys | key | key_len
> | ref | rows | Extra |
>
> +----+-------------+-------+-------+---------------------------+-----------+---------+-------------+------+------------------------------+
> | 1 | PRIMARY | A | const | pathref_2,pathref,version | pathref_2 | 8
> | const,const | 1 | |
> | 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL
> | NULL | NULL | Select tables optimized away |
>
> +----+-------------+-------+-------+---------------------------+-----------+---------+-------------+------+------------------------------+
> 2 rows in set (0.00 sec)
>
> On Wed, 7 Sep 2005, Gleb Paharenko wrote:
>
>> 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
