Optimizing MySQL query with left joins
First, get rid of the subquery.  It is not needed and it can interfere with optimization.  Second, you don't need a left join, because the having clause is requiring matches.

SELECT p .*,
       IF(sum(c.course_id = 2) &gt; 0, 'Yes', 'No') as cTrained,
       IF(sum(c.course_id = 4) &gt; 0, 'Yes', 'No') as aTrained
FROM person p JOIN
     learners_to_classes ltc
     ON p.person_id = ltc.person_id JOIN
     classes c
     ON ltc.class_id = c.class_id
GROUP BY p.person_id
HAVING sum(c.course_id = 4) &gt; 0
ORDER BY p.firstname asc


For this query, you want the obvious indexes:  learners_to_classes(person_id, class_id) and classes(class_id).  The first seems to be missing.