视图再进一步优化！
最近遇到sql优化瓶颈
这是一个插入艺术家数据到es的sql查询，
SELECT
a.id,
a.`name`,
a.pinyin,
a.first_letter,
a.gender,
a.follow_num,
a.art_num,
a.audit_status,
a.create_time,
a.user_id,
a.certificate_status,
b.exhibition_id,
c.avatar,
c.birthday,
d.academy,
d.major,
d.edu_degree,
e.my
FROM artist_artist a
LEFT JOIN (SELECT t2.artist_id,GROUP_CONCAT(t1.exhibition_id SEPARATOR ' ') as exhibition_id FROM exhibition_artwork t1 LEFT JOIN artwork_artwork t2 on t1.artwork_id=t2.id and t2.removed=FALSE WHERE t1.removed=FALSE and t1.selected=TRUE GROUP BY t2.artist_id) as b on a.id=b.artist_id
LEFT JOIN account_user c on a.user_id=c.id
LEFT JOIN (SELECT artist_id,GROUP_CONCAT(academy SEPARATOR ',') as academy,GROUP_CONCAT(major SEPARATOR ',') as major,GROUP_CONCAT(edu_degree SEPARATOR ',') as edu_degree from artist_education WHERE removed=FALSE GROUP BY artist_id) as d on a.id=d.artist_id
LEFT JOIN
(select t3.artist_id,GROUP_CONCAT(CONCAT_WS(',',t3.id,t3.title,t3.category,t3.creation_age,t3.size_l,t3.size_w,t3.size_h,t3.certificate_status,t4.filename) SEPARATOR ';') as my FROM (select s.artist_id,s.id,s.title,s.category,s.creation_age,s.size_l,s.size_w,s.size_h,s.picture_id,s.create_time,s.certificate_status
from artwork_artwork s
where (select count(*) from artwork_artwork
where artist_id = s.artist_id and create_time > s.create_time and removed=FALSE and audit_status=1)<3 and s.removed=FALSE and s.audit_status=1
order by s.create_time desc) as t3 LEFT join artwork_picture t4 on t3.picture_id=t4.id GROUP BY t3.artist_id ) as e on a.id=e.artist_id
WHERE a.removed=FALSE and a.audit_status=1 and a.switch_status=TRUE
这是explain的结果，优化了已经不知何处优化了
因为需要处理的数据太过，所以子查询多，涉及的表也多，左关联的第一张表数据量在七八万，管理以后筛选只要三万，但是取数据的速度太慢了，求各位大神帮看看怎么优化？
