----- Original Message ----- From: "Kevin Burton" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <mysql@lists.mysql.com> 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