mysql分组groupby子查询count优化

[百年菜鸟]

SELECT
a.id,
a.proimg,
a.projectname,
a.datecreated AS date,
a.studyCount AS courseCount,
(SELECT count(1) FROM b WHERE b.proid= a.id) AS materialCount
FROM a
LEFT JOIN c ON c.id = a.projectsource
WHERE 1 = 1
AND a.id NOT IN ('uuceacgkqjlcg5rdlg7zla','uz6eacgkzpnizkuk-ysalw','vsqcacgkpzposa5myqrmxg')
ORDER BY
studyCount DESC,
materialCount DESC,
DATE ASC

[ACMAIN_CHM]

以文本方式贴出(不要贴图!) explain select .... 及 show index from 以供分析