List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:September 8 2005 4:11am
Subject:Re: trouble understanding why query is not using the index.
View as plain text  
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



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