Question

将多条sql查询结果用union all 连接后速度很慢，怎么样解决

要将几个分类的查询结果加在一起，单个查询速度都只有0.2秒左右，但是合在一起就十几秒，试了一下，是第一条查询结果的影响，其他查询结果合在一起速度都没啥问题，但是不知道怎么解决
SELECT
SUM(ps.projectcount) as count,
m.mediatypename,
s.name
FROM
t_project p,
t_projectsize ps,
shannxi.dbo.t_project_surround_value_report ss,
shannxi.dbo.t_surround_district s,
t_mediatype_infor m
WHERE
p.mediatypeinfoid in (
select
id
from
fn_get_mediatype_infor(1)
)
AND ss.dataid = s.id
AND p.id = ss.projectid
AND p.id = ps.projectid
AND p.isdelete=0
AND p.companyid = 2248
AND p.regionid=s.regionid
AND s.regionid = 354
AND ss.datatype=2
AND p.mediatypeinfoid=m.id
GROUP BY
m.mediatypename,
s.name

UNION ALL

SELECT
SUM(ps.projectcount) as count,
m.mediatypename,
s.name
FROM
t_project p,
t_projectsize ps,
shannxi.dbo.t_surround_value_report ss,
shannxi.dbo.bus_station_new sv,
shannxi.dbo.t_surround_district s,
t_mediatype_infor m
WHERE
p.mediatypeinfoid in (
select
id
from
fn_get_mediatype_infor(6)
)
AND ss.dataid = s.id
AND sv.id = ss.projectid
AND p.id = ps.projectid
AND p.dataid = sv.id
AND p.isdelete=0
AND p.companyid = 2248
AND p.regionid=s.regionid
AND s.regionid = 354
AND ss.datatype=4
AND ss.distance=-1
AND p.mediatypeinfoid=m.id
GROUP BY
m.mediatypename,
s.name

Answer 1

把第一个查询中的条件p.mediatypeinfoid in (...)改成内连接:
inner join (...) s0 on p.mediatypeinfoid = s0.id
即用子查询作为内连接的table。

追问

确实快了，缩短到一秒以内了。大神果然高人