优化前的sql
SELECT sl.id,sl.name,sl.province,sl.code,soo.personNum,sl.iscert
FROM 学校表 sl,
(SELECT sch.id,
(SELECT COUNT(DISTINCT usid) FROM 用户组织表 WHERE orgid IN
(SELECT id FROM 组织表 WHERE schid = sch.id UNION ALL SELECT s.id FROM 学校表 s WHERE s.id = sch.id )
) personNum FROM 学校表 sch) soo
WHERE sl.id = soo.id ORDER BY ctime DESC
优化后的sql
SELECT sl.id,sl.name,sl.province,sl.code,soo.personNum,sl.iscert
FROM 学校表 sl LEFT JOIN
(SELECT COUNT(DISTINCT usid) AS personNum,sc.id FROM 用户组织表 uo,
(SELECT o.id,o.schid FROM 组织表 o UNION ALL SELECT s.id,s.id AS schid FROM 学校表 s ) org ,
学校表 sc
WHERE uo.orgid = org.id AND sc.id = org.schid GROUP BY sc.id DESC) soo
ON sl.id = soo.id ORDER BY sl.id DESC
</pre><pre name="code" class="sql">
下图是优化后的sql执行计划
下图是未优化的sql执行计划