complex query - look back on same table data to output results in mysql (VERY slow query)

I'm trying to optimize a query, which as the db is growing, its performance is severely lacking.

Background: we are trying to find a list of users who have taken a course and their credential is now due to be renewed (or has not renewed). In searching the query we have to have a look into the registration table (which is the same table that holds all their registration history) and find records where they have not renewed. (Each time the client takes a course they have a registration record added.) The query I'm wanting to optimize looks to see if they've (client) taken the same course type on a date/time after the last class (of same type) they took. If there is no record it should result row(s) that they didn't renew their course. it sounds easy, but, as you know, when you're in the heat of writing a query it gets very complex--and even more so once the db has grown to be so large that it takes almost 5-6 minutes to find the data. So, I'm asking for help on how I can optimize the efforts of my predecessor, below.

Here is the query, thus far (don't laugh, it wasn't started by me--I took over the project).

I have no clue where to begin with optimizing this MySQL. I think it needs to have select statements within the JOINS, but I'm at your mercy to direct me as to where to start! (I"m not a db guy, but offered to take a look and see where we can fix this).

Thanks a million for reading.

Lee

SELECT
    r.GUID AS `A/C #`,
    concat( a.AttendeeLastName, ', ', a.AttendeeFirstName ) AS Full Name (Last, First),
    r.CourseExpirationDateFull AS `Exp Date`,
    mtype_master_abbrev AS Course,
    a.EmailName AS Email,
    r.EventID,
    r.EventTypeMasterID,
    m.type_master_name, 
    IF( ( r.CourseExpirationDateFull < curdate( ) ), 'Expired', 'Valid' ) AS Status,
    e.StartDateTime,
    ( to_days( curdate( ) ) - to_days( r.ExpNoticeSent ) ) AS Last Notice,
    r.AttendeeID,
    a.AttendeeCredentials,
    r.RegistrationID,
    r.RenewedExternalYYYY,
    r.ExpNoticeSent,
    q.RenewedRegID,
    rs.reg_status_name AS `Reg Status`,
    ( to_days( r.CourseExpirationDateFull ) - to_days( curdate( ) ) ) AS Days2Exp,
    a.flgReturnEmail,
    a.flgSendEmail,
    r.reg_type_ID,
    a._usr_flg_do_not_call,
    a.flgPrintLetter
    e.EventTypeMasterID AS MasterID,
    c.Last: yy-mm-dd - by - topic AS LastComm,
    r.reg_renewal_status_id 
FROM
    vjgzuqrr_wtsql.registration r LEFT JOIN vjgzuqrr_wtsql.events ON ( r.EventID = events.EventID ) LEFT JOIN
    vjgzuqrr_wtsql.attendees a ON a.ID = r.GUID LEFT JOIN 
    vjgzuqrr_wtsql.tbl_crs_type_master m ON r.EventTypeMasterID = m.ID_crs_type_master LEFT JOIN 
    vjgzuqrr_wtsql.qryrenreg q ON r.RegistrationID = q.OrigRegID LEFT JOIN 
    vjgzuqrr_wtsql.tbl_reg_status rs ON rs.ID_reg_status = r.RegistrationStatus LEFT JOIN 
    vjgzuqrr_wtsql.v_last_contact c ON c.registrationid = r.RegistrationID 
WHERE
    r.Role = 1 
    AND r.reg_type_ID IN ( 1, 2 ) 
    AND r.CompletionStatus IN ( 9, 8 ) 
    AND r.r IN ( 1, 14, 9 ) 
    AND ( r.EventTypeMasterID IS NOT NULL OR r.EventTypeMasterID = 17 ) 
    AND r.flgDelete = 0 
    AND r.flgTest = 0 
    AND e.flgDelete = 0 
    AND e.flgTestCourse = 0 
    AND e.flgDelete = 0 
    AND a.flgTest = 0 
    AND isnull( q.RenewedRegID ) 
    AND a.flgReturnEmail = 0 
    AND m.type_master_abbrev NOT IN ( 'EKGPHARM', 'IVCERT', 'sem', 'fam&friends', 'cccc' )

Edit to include Explain: