Query Optimization
Hi Peter,
I am facing an issue with this query, The primary select of the query not using index. i have created index on fields (created_by and created_on) separate and composite both but it works fine in mysql 5.0, but same is not working in mysql 5.6. HELP...???
---------------------------------------------------------------------------------
SELECT this_.id AS id47_0_, this_.as_per_requested AS as2_47_0_, this_.created_by AS created3_47_0_, this_.deal_id AS deal4_47_0_, this_.description AS descript5_47_0_, this_.created_on AS created6_47_0_, this_.modified_on AS modified7_47_0_, this_.drawdown_amount AS drawdown8_47_0_, this_.drawdown_date AS drawdown9_47_0_, this_.dynamic_folder_id AS dynamic10_47_0_, this_.file_message AS file11_47_0_, this_.folder_id AS folder12_47_0_, this_.folder_name AS folder13_47_0_, this_.loan_number AS loan14_47_0_, this_.masterFIleGroupId AS masterF15_47_0_, this_.modified_by AS modified16_47_0_, this_.other_request AS other17_47_0_, this_.request_process_by AS request18_47_0_, this_.request_process_on AS request19_47_0_, this_.request_type_id AS request20_47_0_, this_.response_by AS response21_47_0_, this_.response_comment AS response22_47_0_, this_.response_on AS response23_47_0_, this_.status AS status47_0_, this_.subject AS subject47_0_, (SELECT d.agency_id FROM deal d WHERE d.id=this_.deal_id) AS formula100_0_, (SELECT u.institution_id FROM deal_contacts u WHERE u.user_id = this_.as_per_requested AND u.status = 1) AS formula101_0_, (SELECT gar.comment FROM borrower_lender_to_agency bla JOIN agency_receive ar ON ar.borrower_lender_to_agency_id = bla.id AND ar.status = 1 JOIN group_approval_response gar ON gar.task_id = ar.id AND gar.url_event_id = 58 AND gar.status = 2 WHERE bla.task_id = this_.id AND bla.url_event_id = 57 LIMIT 1) AS formula102_0_, (SELECT r.borrower_approval_required FROM request_confirmation_transfer r WHERE r.status = 1 AND r.request_confirmation_id = this_.id) AS formula103_0_, (SELECT r.borrower_confirm FROM request_confirmation_transfer r WHERE r.status = 1 AND r.request_confirmation_id = this_.id) AS formula104_0_, (SELECT u.first_name FROM deal_contacts u WHERE u.user_id = this_.created_by AND u.status = 1) AS formula105_0_, (SELECT u.institution_id FROM deal_contacts u WHERE u.user_id = this_.created_by AND u.status = 1) AS formula106_0_, (SELECT u.first_name FROM deal_contacts u WHERE u.user_id = this_.created_by AND u.status = 1) AS formula107_0_, (SELECT i.institution_name FROM deal_contacts u JOIN institutions i ON u.institution_id = i.id WHERE u.user_id = this_.created_by AND u.status = 1) AS formula108_0_, (SELECT dc.relationship FROM deal_contacts dc WHERE dc.status = 1 AND dc.user_id = this_.created_by LIMIT 1) AS formula109_0_, (SELECT d.if_active FROM deal d WHERE d.id = this_.deal_id) AS formula110_0_, (SELECT d.dealName FROM deal d WHERE d.id = this_.deal_id) AS formula111_0_, (SELECT d.deal_number FROM deal d WHERE d.id=this_.deal_id) AS formula112_0_, (SELECT r.deem_consent FROM transfer_certificate_forward r WHERE r.request_id = this_.id LIMIT 1) AS formula113_0_, (SELECT gar.id FROM request_confirmation rc JOIN borrower_lender_to_agency bla ON rc.id=bla.task_id JOIN agency_receive ar ON bla.id=ar.borrower_lender_to_agency_id JOIN group_approval_response gar ON ar.id=gar.task_id WHERE rc.request_type_id IN (160,219) AND bla.url_event_id=57 AND gar.url_event_id=58 AND rc.id = this_.id AND gar.status NOT IN (3,5) LIMIT 1) AS formula114_0_, (SELECT r.if_confirm FROM request_confirmation_transfer r WHERE r.status = 1 AND r.request_confirmation_id = this_.id) AS formula115_0_, (SELECT r.lender_name FROM request_confirmation_transfer r WHERE r.status = 1 AND r.request_confirmation_id = this_.id) AS formula116_0_, (SELECT r.notification FROM transfer_certificate_forward r WHERE r.request_id = this_.id) AS formula117_0_, (SELECT CONCAT('upload/',ff.actual_folder_name,'/',f.actual_folder_name) FROM folders f JOIN folders ff ON ff.id=f.parent_folder_id WHERE f.id=this_.dynamic_folder_id) AS formula118_0_, (SELECT COUNT(r.id) FROM request_confirmation r WHERE r.deal_id = this_.deal_id AND r.request_type_id = 156 AND r.status NOT IN (6,7,22)) AS formula119_0_, (SELECT r.propose_transfer_date FROM request_confirmation_transfer r WHERE r.status = 1 AND r.request_confirmation_id = this_.id) AS formula120_0_, (SELECT r.id FROM request_confirmation_transfer r WHERE r.status = 1 AND r.request_confirmation_id = this_.id) AS formula121_0_, (SELECT m.type_name FROM master_typedetail m WHERE m.id = this_.request_type_id) AS formula122_0_, (SELECT dc.first_name FROM request_confirmation rc JOIN deal_contacts dc ON dc.user_id=rc.created_by JOIN master_typedetail m ON m.id=dc.relationship WHERE rc.status='5' AND rc.id=this_.id AND dc.status = 1 LIMIT 1) AS formula123_0_, (SELECT u.first_name FROM deal_contacts u WHERE u.user_id = this_.as_per_requested AND u.status = 1) AS formula124_0_, (SELECT u.first_name FROM deal_contacts u WHERE u.user_id = this_.response_by AND u.status = 1) AS formula125_0_, (SELECT r.response_deadline_date FROM transfer_certificate_forward r WHERE r.request_id = this_.id) AS formula126_0_, (SELECT r.transactor_transfer_date FROM request_confirmation_transfer r WHERE r.status = 1 AND r.request_confirmation_id = this_.id) AS formula127_0_, (SELECT r.transfer FROM request_confirmation_transfer r WHERE r.status = 1 AND r.request_confirmation_id = this_.id) AS formula128_0_ FROM request_confirmation this_ WHERE (SELECT u.institution_id FROM deal_contacts u WHERE u.user_id = this_.created_by AND u.status = 1)=21 ORDER BY this_.created_on DESC;
------------------------------------------------------------------------------
Explain Plan in mysql 5.6
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "PRIMARY" "this_" "ALL" \N \N \N \N "909" "Using where; Using filesort"
"31" "DEPENDENT SUBQUERY" "u" "ref" "Index_UserId" "Index_UserId" "5" "loadtest.this_.created_by" "1" "Using where"
"30" "DEPENDENT SUBQUERY" "r" "ref" "Index_ReqID" "Index_ReqID" "5" "loadtest.this_.id" "1" "Using where"
"29" "DEPENDENT SUBQUERY" "r" "ref" "Index_ReqID" "Index_ReqID" "5" "loadtest.this_.id" "1" "Using where"
"28" "DEPENDENT SUBQUERY" "r" "ref" "Index_request_id" "Index_request_id" "5" "loadtest.this_.id" "1" \N
"27" "DEPENDENT SUBQUERY" "u" "ref" "Index_UserId" "Index_UserId" "5" "loadtest.this_.response_by" "1" "Using where"
"26" "DEPENDENT SUBQUERY" "u" "ref" "Index_UserId" "Index_UserId" "5" "loadtest.this_.as_per_requested" "1" "Using index condition; Using where"
"25" "DEPENDENT SUBQUERY" "rc" "eq_ref" "PRIMARY" "PRIMARY" "4" "loadtest.this_.id" "1" "Using where"
"25" "DEPENDENT SUBQUERY" "dc" "ref" "Index_UserId" "Index_UserId" "5" "loadtest.rc.created_by" "1" "Using where"
"25" "DEPENDENT SUBQUERY" "m" "eq_ref" "PRIMARY,Index_master_typedetail_id" "PRIMARY" "4" "loadtest.dc.relationship" "1" "Using index"
"24" "DEPENDENT SUBQUERY" "m" "eq_ref" "PRIMARY,Index_master_typedetail_id" "PRIMARY" "4" "loadtest.this_.request_type_id" "1" \N
"23" "DEPENDENT SUBQUERY" "r" "ref" "Index_ReqID" "Index_ReqID" "5" "loadtest.this_.id" "1" "Using where"
"22" "DEPENDENT SUBQUERY" "r" "ref" "Index_ReqID" "Index_ReqID" "5" "loadtest.this_.id" "1" "Using where"
"21" "DEPENDENT SUBQUERY" "r" "ref" "Index_MasterTypeDetailId,Index_DealId" "Index_DealId" "5" "loadtest.this_.deal_id" "151" "Using where"
"20" "DEPENDENT SUBQUERY" "f" "eq_ref" "PRIMARY,FK_folders" "PRIMARY" "4" "loadtest.this_.dynamic_folder_id" "1" \N
"20" "DEPENDENT SUBQUERY" "ff" "eq_ref" "PRIMARY" "PRIMARY" "4" "loadtest.f.parent_folder_id" "1" \N
"19" "DEPENDENT SUBQUERY" "r" "ref" "Index_request_id" "Index_request_id" "5" "loadtest.this_.id" "1" \N
"18" "DEPENDENT SUBQUERY" "r" "ref" "Index_ReqID" "Index_ReqID" "5" "loadtest.this_.id" "1" "Using where"
"17" "DEPENDENT SUBQUERY" "r" "ref" "Index_ReqID" "Index_ReqID" "5" "loadtest.this_.id" "1" "Using where"
"16" "DEPENDENT SUBQUERY" "gar" "range" "Index_TaskId" "Index_TaskId" "5" \N "1" "Using index condition; Using where"
"16" "DEPENDENT SUBQUERY" "rc" "eq_ref" "PRIMARY,Index_MasterTypeDetailId" "PRIMARY" "4" "loadtest.this_.id" "1" "Using where"
"16" "DEPENDENT SUBQUERY" "bla" "ref" "PRIMARY,Index_RequestId" "Index_RequestId" "5" "loadtest.this_.id" "1" "Using index condition; Using where"
"16" "DEPENDENT SUBQUERY" "ar" "eq_ref" "PRIMARY,Index_bla.id" "PRIMARY" "4" "loadtest.gar.task_id" "1" "Using where"
"15" "DEPENDENT SUBQUERY" "r" "ref" "Index_request_id" "Index_request_id" "5" "loadtest.this_.id" "1" \N
"14" "DEPENDENT SUBQUERY" "d" "eq_ref" "PRIMARY" "PRIMARY" "4" "loadtest.this_.deal_id" "1" \N
"13" "DEPENDENT SUBQUERY" "d" "eq_ref" "PRIMARY" "PRIMARY" "4" "loadtest.this_.deal_id" "1" \N
"12" "DEPENDENT SUBQUERY" "d" "eq_ref" "PRIMARY" "PRIMARY" "4" "loadtest.this_.deal_id" "1" \N
"11" "DEPENDENT SUBQUERY" "dc" "ref" "Index_UserId" "Index_UserId" "5" "loadtest.this_.created_by" "1" "Using where"
"10" "DEPENDENT SUBQUERY" "u" "ref" "Index_institution_id,Index_UserId" "Index_UserId" "5" "loadtest.this_.created_by" "1" "Using where"
"10" "DEPENDENT SUBQUERY" "i" "eq_ref" "PRIMARY" "PRIMARY" "4" "loadtest.u.institution_id" "1" \N
"9" "DEPENDENT SUBQUERY" "u" "ref" "Index_UserId" "Index_UserId" "5" "loadtest.this_.created_by" "1" "Using where"
"8" "DEPENDENT SUBQUERY" "u" "ref" "Index_UserId" "Index_UserId" "5" "loadtest.this_.created_by" "1" "Using where"
"7" "DEPENDENT SUBQUERY" "u" "ref" "Index_UserId" "Index_UserId" "5" "loadtest.this_.created_by" "1" "Using where"
"6" "DEPENDENT SUBQUERY" "r" "ref" "Index_ReqID" "Index_ReqID" "5" "loadtest.this_.id" "1" "Using where"
"5" "DEPENDENT SUBQUERY" "r" "ref" "Index_ReqID" "Index_ReqID" "5" "loadtest.this_.id" "1" "Using where"
"4" "DEPENDENT SUBQUERY" "gar" "range" "Index_TaskId" "Index_TaskId" "5" \N "1" "Using index condition; Using where"
"4" "DEPENDENT SUBQUERY" "bla" "ref" "PRIMARY,Index_RequestId" "Index_RequestId" "5" "loadtest.this_.id" "1" "Using where"
"4" "DEPENDENT SUBQUERY" "ar" "eq_ref" "PRIMARY,Index_bla.id" "PRIMARY" "4" "loadtest.gar.task_id" "1" "Using where"
"3" "DEPENDENT SUBQUERY" "u" "ref" "Index_UserId" "Index_UserId" "5" "loadtest.this_.as_per_requested" "1" "Using index condition; Using where"
"2" "DEPENDENT SUBQUERY" "d" "eq_ref" "PRIMARY" "PRIMARY" "4" "loadtest.this_.deal_id" "1" \N