mysql 环境下的sql优化

优化前的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执行计划