Query Performance – Optimize multiple inner JOIN

Consider the following query:

    mysql> SELECT GROUP_CONCAT (a.id SEPARATOR & # 39; n)
-> FROM assignment a
-> INNER JOIN statements s ON a.id = s.assignment_id
-> INNER JOIN notification_status_log asl ON a.status_log_id = asl.id
-> INNER JOIN notification_status ags ON asl.status_id = ags.id
-> WO ags.category <> & # 39; PASSED & # 39;
-> AND ags.category <> & # 39; TEST & # 39;
-> AND ags.category <> & # 39; REJECTED & # 39;
-> AND ags.category <> & # 39; ABORTED & # 39;
-> AND ags.category <> & # 39; NA & # 39;
-> AND ID IS NOT ZERO
-> AND s.id IN (SELECT MAX (sts.id) AS dctrn__1 FROM Assignment agr INNER JOIN statements sts ON agr.id = sts.assignment_id WHERE agr.id = a.id)
-> AND s.grade> 0
-> AND ags.types <> & # 39; CB & # 39;
-> AND ags.types <> & # 39; CD & # 39;
-> AND ags.types <> & # 39; CE & # 39;
-> AND ags.types <> & # 39; CF & # 39;
-> GROUP BY a.id;

I would like to have an idea how to fix this, which takes so long. I added an index for the "s.grades" and "ags.types" columns TO JOIN instead of in WHERE clause. It is currently running for almost 2 hours, at a record 500,000. Dreadful.

Any insight or indication of what affects performance is highly appreciated.