List: | General Discussion | « Previous MessageNext Message » | |
From: | DBA | Date: | June 7 2005 9:22pm |
Subject: | Re: Seriously.. When are we going to get subqueries?! | ||
View as plain text |
----- 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