Hello.
In my opinion - yes it is, however, trust only the benchmarks :) Jason Pyeron <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
