有两个表,数据都上10万条,原来的跨表count的语句是这样的:
select ID,(select Count(*) from A2 where A2.A1ID = A1.ID) as A2Count from A1
感觉速度挺慢的,怎么能优化呢?
select ID,(select Count(*) from A2 where A2.A1ID = A1.ID) as A2Count from A1
感觉速度挺慢的,怎么能优化呢?
from A1 AS a
LEFT OUTER JOIN A2 AS b
on a.ID=b.A1ID
group by ID
--加索引
create index A1_id on A1(id)
create index A2_a1id on A2(A1id)
SELECT ID,b.A2Count
FROM A1,
(
SELECT A1ID,COUNT(*) as A2Count
FROM A2
GROUP BY A1ID
)b
WHERE A1.ID=b.A1ID --若表A1上的ID有索引会更快.
from A1 AS a
LEFT OUTER JOIN A2 AS b
on a.ID=b.A1ID
group by a.ID