----- Original Message -----
From: "Kevin Burton" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[email protected]>
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
