bad query that hangs

200599

SELECT DISTINCT applicants_emails.id AS t0_r0, applicants_emails.applicant_id AS t0_r1, applicants_emails.email_id AS t0_r2, applicants_emails.status_description AS t0_r3, applicants_emails.created_at AS t0_r4, applicants_emails.updated_at AS t0_r5, applicants.id AS t1_r0, applicants.ssn AS t1_r1, applicants.created_at AS t1_r2, applicants.updated_at AS t1_r3, applicants.score AS t1_r4, applicants.identifier AS t1_r5, applicants.email AS t1_r6, applicants.last_name AS t1_r7, applicants.middle_name AS t1_r8, applicants.first_name AS t1_r9, applicants.suffix AS t1_r10, applicants.title AS t1_r11, applicants.last_refresh AS t1_r12, applicants.date_added AS t1_r13, applicants.cas_id AS t1_r14, applicants.association_id AS t1_r15, applicants.application_complete AS t1_r16, applicants.verified AS t1_r17, applicants.verified_date AS t1_r18, applicants.professional_pin AS t1_r19, applicants.match_id AS t1_r20, applicants.pre_submission_release_authorized AS t1_r21, applicants.photo_file_name AS t1_r22, applicants.photo_content_type AS t1_r23, applicants.photo_file_size AS t1_r24, applicants.photo_updated_at AS t1_r25, applicants.id_number AS t1_r26, applicants.id_number_type AS t1_r27, applicants.visible_for_advisor AS t1_r28, applicants.direct_applicant_complete AS t1_r29, applicants.email_type_id AS t1_r30, applicants.dentpin AS t1_r31, applicants.date_submitted AS t1_r32, emails.id AS t2_r0, emails.body AS t2_r1, emails.created_at AS t2_r2, emails.subject AS t2_r3, emails.user_id AS t2_r4, emails.bcc_user AS t2_r5, emails.reply_to_user AS t2_r6, emails.cc_user AS t2_r7, emails.sent_at AS t2_r8, emails.email_template_id AS t2_r9, emails.enqueued_at AS t2_r10, emails.user_identity_id AS t2_r11, emails.updated_at AS t2_r12, emails.priority AS t2_r13, applicants_emails.id, applicants_emails.*, designations.* FROM applicants_emails INNER JOIN emails ON emails.id = applicants_emails.email_id INNER JOIN applicants ON applicants.id = applicants_emails.applicant_id INNER JOIN designations ON designations.applicant_id = applicants.id LEFT OUTER JOIN statuses ON statuses.id = designations.application_status_id AND statuses.type IN ('ApplicationStatus') WHERE applicants_emails.applicant_id IN (SELECT DISTINCT applicants.id FROM applicants WHERE applicants.association_id = 313 AND applicants.id IN (SELECT applicant_designation_details_mv.applicant_id FROM applicant_designation_details_mv WHERE applicant_designation_details_mv.program_id IN (SELECT user_identity_programs.program_id FROM user_identity_programs WHERE user_identity_programs.user_identity_id = 209110) AND applicant_designation_details_mv.status_show = 't')) AND emails.user_identity_id IN (207631, 207887, 208060, 208161, 208261, 208352, 208463, 208603, 208773, 209083, 209110, 213014, 217538, 219549, 220432, 220435, 220444, 220451, 220459, 220473, 220477, 220484, 220491, 220496, 220503, 220517, 220540, 220547, 221925, 221926, 221927, 221928, 221929, 245118) AND statuses.show = 't' AND designations.program_id IN (SELECT programs.id FROM programs INNER JOIN user_identity_programs ON programs.id = user_identity_programs.program_id WHERE programs.type IN ('Program') AND user_identity_programs.user_identity_id = 209110) AND designations.applicant_id IN (SELECT DISTINCT applicants.id FROM applicants WHERE applicants.association_id = 313 AND applicants.id IN (SELECT applicant_designation_details_mv.applicant_id FROM applicant_designation_details_mv WHERE applicant_designation_details_mv.program_id IN (SELECT user_identity_programs.program_id FROM user_identity_programs WHERE user_identity_programs.user_identity_id = 209110) AND applicant_designation_details_mv.status_show = 't')) AND designations.local_status_id = 200599 ORDER BY designations.rank ASC, designations.school_name ASC, applicants.last_name, emails.created_at DESC