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

-- 
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
-                                                               -
- Jason Pyeron                      PD Inc. http://www.pdinc.us -
- Partner & Sr. Manager             7 West 24th Street #100     -
- +1 (443) 921-0381                 Baltimore, Maryland 21218   -
-                                                               -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

This message is for the designated recipient only and may contain 
privileged, proprietary, or otherwise private information. If you 
have received it in error, purge the message from your system and 
notify the sender immediately.  Any other use of the email by you 
is prohibited.
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