I have the following query which contains 3 subqueries.
For medium to large datasets, this query takes a very
long time and usually runs out of memory. Is there a way
of condensing this query or making it more efficient? It
would also be nice to not have sitenum= repeated in every
subquery. This query also returns the number of rows for
a particular site but I'm interested in a count for all
sites. Rather than run this separately for each site,
ideally the end result will simply look something like:

sitenum count(fam4.fid)
-------------------------
2 0
4 1
5 3
7 1
9 2
...

here is the query:

select fam4.fid from site s4
inner join family fam4 on s4.asid=fam4.asid
inner join individual ind4 on fam4.afid=ind4.afid
where s4.sitenum=9 and ind4.iq=2 and fam4.fid in
( select fam3.fid from site s3
inner join family fam3 on s3.asid=fam3.asid
inner join individual ind3 on fam3.afid=ind3.afid
where s3.sitenum=9 and ind3.iq=1 and fam3.fid in
( select fam2.fid from site s2
inner join family fam2 on s2.asid=fam2.asid
inner join individual ind2 on fam2.afid=ind2.afid
where s2.sitenum=9 and (ind2.psd>36 and
ind2.psd<900) and fam2.fid in
( select fam.fid from site s
inner join family fam on s.asid=fam.asid
inner join individual ind on fam.afid=ind.afid
where s.sitenum=9 and (ind.aut=2 or ind.aut=5)
group by fam.fid
having count(ind.iid)>=2
)
group by fam2.fid
having count(ind2.iid)>=2
)
group by fam3.fid
having count(ind3.iid)=0 or count(ind3.iid)=1
)
group by fam4.fid
having count(ind4.iid)>=1


MySQL LazyDBA home page