关于加速查询的问题我的数据库里有4个table,表t0 t1 t2 t3,数据都是1对多的关系,对于t0的每条记录,怎样快速统计出t1 t2 t3中对应的记录数。我在程序中用了select count(*) from t2 where f2 in (select …… from ……) 很慢
尤其是统计t3中对应的记录数时 非常慢 t0中大概有17条记录
t1中大概有2000条记录
t2中大概有9万条记录
t3中大概有15万条记录有什么办法可以解决?
尤其是统计t3中对应的记录数时 非常慢 t0中大概有17条记录
t1中大概有2000条记录
t2中大概有9万条记录
t3中大概有15万条记录有什么办法可以解决?
Where 條件
t0.id,
t1cnt,
t2cnt,
t3cnt
from t0 left join (
select id,count(*) as t1cnt
from t1 group by id
) as t11
on t0.id=t11.id
left join (
select id,count(*) as t2cnt
from t2 group by id
) as t21
on t0.id=t21.id
left join (
select id,count(*) as t3cnt
from t3 group by id
) as t31
on t0.id=t31.id
select f0,Count(distinct f1),Count(distinct f2),Count(distinct f3)
from t0 left outer join t1 on t0.f0=t1.f1
left outer join t2 on t0.f0=t2.f2
left outer join t3 on t0.f0=t3.f3
t1.id是t2的外键
t2.id是t3的外键
table field1(PK) field2(FK)
t0 id
t1 t1id id
t2 t2id t1id
t3 t3id t2id