Optimizing MySQL query with left joins Ok, so first off i am going to provide a bit of information on the database structure

I have 3 indexed tables that are used in this query (though one of them should probably be a weak entity, but this is how it was when i took over) 

person
person_id (pk)

classes
class_id (pk)
course_id (fk)

learners_to_classes
id (pk)
person_id (fk)
class_id (fk)


The persons Table has about 46,100 records
learners_to_classes has about 51,100 records
and classes has about 1670 records.

The query i am working on is built dynamically. I am working on a person search feature which will list the records from person, plus an indicator if that person has completed 2 particular course types, based on a number of parameters.

there issue i come across how ever is the query time for when the only parameters supplied is either none, (which would probably be a rare use) or a combination of the course types has been completed.

so for example. i want a list of people who have aTrained set to Yes would produce the following query:

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)
LEFT JOIN
    (SELECT 
        `ltc`.`person_id`, `c`.`class_id`, `c`.`course_id`
    FROM
        (`classes` c)
    INNER JOIN `learners_to_classes` ltc ON `ltc`.`class_id` = `c`.`class_id`) AS c ON `c`.`person_id` = `p`.`person_id`
GROUP BY `p`.`person_id`
HAVING sum(c.course_id = 4) &gt; 0
ORDER BY p.`firstname` asc


searching for people who have cTrained='Yes' and a aTrained='Yes' generates the following query:

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


These queries how ever are taking over 100 seconds to execute,
and i cant seem to think of a way to optimize them. 
I was hoping someone one here might have an idea
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.