Optimise multiple inner JOIN

Question

Consider the following query:

 mysql> SELECT GROUP_CONCAT(a.id SEPARATOR '\n')
    -> FROM assignment a
    -> INNER JOIN statements s ON a.id = s.assignment_id
    -> INNER JOIN assignment_status_log asl ON a.status_log_id = asl.id
    -> INNER JOIN assignment_status ags ON asl.status_id = ags.id
    -> WHERE ags.category <> 'PASSED'
    -> AND ags.category <> 'TEST'
    -> AND ags.category <> 'REJECTED'
    -> AND ags.category <> 'CANCELED'
    -> AND ags.category <> 'NA'
    -> AND s.id IS NOT NULL
    -> 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 <> 'CB'
    -> AND ags.types <> 'CD'
    -> AND ags.types <> 'CE'
    -> AND ags.types <> 'CF'
    -> GROUP BY a.id;

I would like to have an idea as to how I can troubleshoot what is taking so long. I have added index on s.grades and ags.types column, and am pondering if I would need to try to have the conditions on the JOIN instead of in WHERE clause. It has currently been running for close to 2 hours, on 500 000 record. Awful.

Any insight or hints as to what is affecting the performance on this will be greatly appreciated.