Re: Seriously.. When are we going to get subqueries?!

[DBA]

----- Original Message ----- 
From: "Kevin Burton" <burton@stripped>
To: <greg@stripped>
Cc: <mysql@stripped>
Sent: Tuesday, June 07, 2005 6:11 PM
Subject: Re: Seriously.. When are we going to get subqueries?!


> Greg Whalin wrote:
> 
>> They do use indexes if you use them to build derived tables and are 
>> pretty fast.  The only case where I see them not using indexes when I 
>> think they should is when you use a sub-query for an IN() clause.
> 
> I'm sorry.. yes.. They're not using indexes when within IN clauses which 
> for me is 99.9% of the time.
> 
> Maybe I'm wrong though and this is a rare but I don't think so...
> 
> Kevin

I use subqueries and I thought that it was using the index? I might be wrong? This is
MySQL on 4.1.11-nt. Here is the explain plan:

mysql> explain select count(*) from ptt_trans_06 where trans='ADD' and nai in (select
> scr_userid from ptt_home where number_of_sessions>3);
+----+--------------------+--------------+----------------+---------------+--------------+---------+------+-------+--------------------------+
| id | select_type        | table        | type           | possible_keys | key          |
key_len | ref  | rows  | Extra
           |
+----+--------------------+--------------+----------------+---------------+--------------+---------+------+-------+--------------------------+
|  1 | PRIMARY            | ptt_trans_06 | ALL            | NULL          | NULL         |
   NULL | NULL | 21621 | Using where
           |
|  2 | DEPENDENT SUBQUERY | ptt_home     | index_subquery | ptt_home_nai  | ptt_home_nai |
    250 | func |     1 | Using index; U
sing where |
+----+--------------------+--------------+----------------+---------------+--------------+---------+------+-------+--------------------------+

Isn't this using an index?

I would agree that I use a lot of subqueries using the IN()  clause.

Lawrence Kennon