why does this query take more than 3 minutes:
select Count (*) from S_Address Where KundenID=18 And email in (select
distinct email from s_pixelemail where mailid=51)


select Count (*) from S_Address Where KundenID=18
==> takes 0 sec -> 3500 rows

select distinct email from s_pixelemail where mailid=51
==> takes 0 sec -> 1260 rows


then i made a inner join:
SELECT count(distinct s_address.email)
FROM s_address INNER JOIN
s_pixelemail ON s_address.EMail =
s_pixelemail.EMail
WHERE (s_address.KundenID = 18) AND (s_pixelemail.MailID = 51)
==> takes also 0 sec


so why does the first query take more than 3 minutes??


thanks!
Nedo

Because you're running the subquery for every row in the initial query.
Look at it this way: if S_Address has 4 million rows in it, it's going
to run that second query 4 million times the way it's currently
written. And if the zero seconds you got from the 1260 rows query is
really .0005 seconds, then it's going to take 20000 seconds.

A join (like your final query) is the right way to do this, although to
get the exact same result, it should be a LEFT join rather than an
INNER join.

hmm... i will observe this effect in future... :-)
thanks


Nedo