How to optimize this query

[vaskarbasak]

Hello,

It takes lots of time to execute, please help me to optimize this query:

SELECT m.*
FROM
(
SELECT appoAppointmentHeader.hdrID,1 AS 'singleStepResource',
appoAppointmentHeader.formatID,
appoTypeMaster.titleBgColor AS appoHeaderBgColor,
appoTypeMaster.titleTextColor AS appoHeaderFontColor, appoTypeMaster.bodyBgColor AS procBgColour,
appoTypeMaster.bodyTextColor AS procFontColour, appoTypeMaster.appoTypeTitle AS appoTypeDesc,
appoAppointmentHeader.ptntPatientID, appoAppointmentHeader.appoDate,
appoAppointmentHeader.sec1UserLoginOUID AS loginOUID, TRIM(COALESCE(appoAppointmentHeaderLang.notes,' '))AS notes,
appoAppointmentHeader.appoTypeID, appoAppointmentHeader.isEmergency,
DATE_FORMAT(appoAppointmentProcedureSteps.appoProcExpStartTime,'%H:%i:%s') AS appoProcExpStartTime,
DATE_FORMAT(appoAppointmentProcedureSteps.appoProcExpEndTime,'%H:%i:%s') AS appoProcExpEndTime,
IF(patientNickName = '',CONCAT(COALESCE(ptntPatientDetails.patientFName,''),' ',COALESCE(ptntPatientDetails.patientMName,''),' ',COALESCE(ptntPatientDetails.patientLName,''))
,CONCAT(COALESCE(ptntPatientDetails.patientNickName,''),' ',COALESCE(ptntPatientDetails.patientLName,''))
) AS patientName,
CONCAT( COALESCE(ptntPatientDetails.patientSalutaion,''),' ',
COALESCE(ptntPatientDetails.patientFName,''),' ',
COALESCE(ptntPatientDetails.patientMName,''),' ',
COALESCE(ptntPatientDetails.patientLName,'')
) AS patientFullName,
TIMEDIFF(appoAppointmentProcedureSteps.appoProcExpEndTime, appoAppointmentProcedureSteps.appoProcExpStartTime) AS Duration,
GetPatientAge(ptntPatientDetails.patientID) AS age,
COALESCE(ptntPatientDetails.sex,'') AS sex, ptntPatientDetails.homePhone,
appoAppointmentResources.pracProviderID as  providerID, appoAppointmentResources.pracResourceID AS resourceID,
pracProviderToProviderTypeMap.pracProviderTypeID AS entityTypeID,
appoAppointmentResources.dtlID, appoAppointmentHeader.currentAppoHdrStatusID,
pracStatusMasterLang.statusDesc, appoAppointmentHeader.currentAppoHdrStatusID AS pracStatusID,
CASE appoAppointmentHeader.currentAppoHdrStatusID
WHEN 800 THEN pracStatusMaster.bgColour
WHEN 760 THEN pracStatusMaster.bgColour
ELSE 
IF( appoAppointmentHeader.autoUpdateStatusColor = '',pracStatusMaster.bgColour,appoAppointmentHeader.autoUpdateStatusColor)
END AS statusBGColour, pracStatusMaster.fontColour AS statusFontColour,
DATE_FORMAT(creationDate,'%Y-%m-%d %H:%i') AS creationDate,
fnc_GetAppoProviderResourceName(appoAppointmentProcedureSteps.appoHdrID,5,1) AS providerResource,
appoAppointmentResources.appoPositions,
fnc_GetAppointmentProviderResourceName(appoAppointmentProcedureSteps.appoHdrID,2,'N',1) AS providerName,
fnc_GetAppointmentProviderResourceName(appoAppointmentProcedureSteps.appoHdrID,3,'N',1) AS resourceName,
fnc_GetAppointmentProviderResourceNameByPercentage(appoAppointmentProcedureSteps.appoHdrID,2,'N',1) AS modifyproviderName,
fnc_GetAppointmentProviderResourceNameByPercentage(appoAppointmentProcedureSteps.appoHdrID,3,'N',1) AS modifyresourceName,
tpAppointmentPlan.appointmentPlanID, tpAppointmentPlan.number,pracStatusMaster.statusType,
ptntPatientDetails.workPhone , ptntPatientDetails.cellPhone, ptntPatientDetails.primaryPhone,
COALESCE(ptntPatientDetails.patientFName,'')patientFName,COALESCE(ptntPatientDetails.patientLName,'')patientLName,
COALESCE(ptntPatientDetails.patientNickName,'')patientNickName,ptntPatientDetails.patientID,
ROUND(appoTypeMaster.appoTypeUnit/5.00) AS appoTypeUnit,appoAppointmentHeader.officeID,
appoAppointmentHeader.createBy,pracOffice.practiceName AS practiceName,IF( ptntPatientDetails.patientID =5001,0,fnc_GetRecallFlag(ptntPatientDetails.patientID)) AS recallFlag,
ptntPatientDetails.officeID AS ptntofficeID,
appoAppointmentHeader.desiredDate,
appoAppointmentHeader.priority,
appoAppointmentHeader.isSCL,
pracProviderMaster.providerCode,
pracProviderMaster.shortCode,
appoAppointmentHeader.appoUnscheduledCreatedFromID,
appoAppointmentHeader.clipBoardCreationDate,
appoAppointmentHeader.SCLCreationDate,
ptntPatientDetails.patientCode
FROM 
appoAppointmentHeader
INNER JOIN  appoAppointmentHeaderLang ON appoAppointmentHeader.hdrID = appoAppointmentHeaderLang.appoHdrID
INNER JOIN  appoAppointmentProcedureSteps ON  appoAppointmentProcedureSteps.appoHdrID=appoAppointmentHeader.hdrID
INNER JOIN  appoAppointmentResources ON appoAppointmentResources.appoHdrID=appoAppointmentHeader.hdrID
INNER JOIN ptntPatientDetails ON  ptntPatientDetails.patientID=appoAppointmentHeader.ptntPatientID AND ptntPatientDetails.isDeleted=0 AND ptntPatientDetails.patientID<>5001
INNER JOIN pracProviderMaster ON  pracProviderMaster.providerID=appoAppointmentResources.pracProviderID AND pracProviderMaster.isDeleted=0 
INNER JOIN pracProviderToProviderTypeMap ON pracProviderMaster.providerID = pracProviderToProviderTypeMap.pracProviderID
INNER JOIN appoTypeMaster ON appoTypeMaster.typeID=appoAppointmentHeader.appoTypeID
INNER JOIN appoTypeMasterLang ON appoTypeMasterLang.appoTypeID=appoTypeMaster.typeID
INNER JOIN pracStatusMaster ON pracStatusMaster.statusID=appoAppointmentHeader.currentAppoHdrStatusID
INNER JOIN pracStatusMasterLang ON pracStatusMasterLang.pracStatusID=pracStatusMaster.statusID AND pracStatusMasterLang.langLangID = 1
LEFT OUTER JOIN tpAppointmentPlan ON appoAppointmentHeader.appoPlanID = tpAppointmentPlan.appointmentPlanID
LEFT OUTER JOIN pracOffice ON pracOffice.officeID=appoAppointmentHeader.officeID
LEFT OUTER JOIN appoUnscheduledCreatedFrom ON   appoUnscheduledCreatedFrom.appoUnscheduledCreatedFromID = appoAppointmentHeader.appoUnscheduledCreatedFromID
WHERE 
(
  appoAppointmentHeader.currentAppoHdrStatusID=100

OR
  (
    appoAppointmentHeader.appoDate >= fnc_getOrgCurDate()
    AND  FIND_IN_SET(appoAppointmentHeader.currentAppoHdrStatusID,'700,710,716,711,712,713,714,715,717,5002,5004,5005,5013,5014,6004,6005')>0   
    AND appoAppointmentHeader.isSCL='1'
  )
  OR
  (
       appoAppointmentHeader.currentAppoHdrStatusID=200
       AND appoAppointmentHeader.isSCL='1'
  )  ) UNION ALL
SELECT appoAppointmentHeader.hdrID,1 AS 'singleStepResource',
appoAppointmentHeader.formatID,
appoTypeMaster.titleBgColor AS appoHeaderBgColor,
appoTypeMaster.titleTextColor AS appoHeaderFontColor, appoTypeMaster.bodyBgColor AS procBgColour,
appoTypeMaster.bodyTextColor AS procFontColour, appoTypeMaster.appoTypeTitle AS appoTypeDesc,
appoAppointmentHeader.ptntPatientID, appoAppointmentHeader.appoDate,
appoAppointmentHeader.sec1UserLoginOUID AS loginOUID, TRIM(COALESCE(appoAppointmentHeaderLang.notes,' ')) AS notes,
appoAppointmentHeader.appoTypeID, appoAppointmentHeader.isEmergency,
DATE_FORMAT(appoAppointmentProcedureSteps.appoProcExpStartTime,'%H:%i:%s') AS appoProcExpStartTime,
DATE_FORMAT(appoAppointmentProcedureSteps.appoProcExpEndTime,'%H:%i:%s') AS appoProcExpEndTime,
IF(patientNickName = '',CONCAT(COALESCE(ptntPatientDetails.patientFName,''),' ',COALESCE(ptntPatientDetails.patientMName,''),' ',COALESCE(ptntPatientDetails.patientLName,''))
,CONCAT(ptntPatientDetails.patientNickName,' ', ptntPatientDetails.patientLName)
) AS patientName,
CONCAT( COALESCE(ptntPatientDetails.patientSalutaion,''),' ',
COALESCE(ptntPatientDetails.patientFName,''),' ',
COALESCE(ptntPatientDetails.patientMName,''),' ',
COALESCE(ptntPatientDetails.patientLName,'')
) AS patientFullName,
TIMEDIFF(appoAppointmentProcedureSteps.appoProcExpEndTime, appoAppointmentProcedureSteps.appoProcExpStartTime) AS Duration,
GetPatientAge(ptntPatientDetails.patientID) AS age,
COALESCE(ptntPatientDetails.sex,'') AS sex, ptntPatientDetails.homePhone,
appoAppointmentResources.pracProviderID AS providerID, appoAppointmentResources.pracResourceID AS resourceID,
pracResourceMaster.resourceTypeID AS entityTypeID,
appoAppointmentResources.dtlID,appoAppointmentHeader.currentAppoHdrStatusID,
pracStatusMasterLang.statusDesc, appoAppointmentHeader.currentAppoHdrStatusID AS pracStatusID,
CASE appoAppointmentHeader.currentAppoHdrStatusID
WHEN 800 THEN pracStatusMaster.bgColour
WHEN 760 THEN pracStatusMaster.bgColour
ELSE 
IF( appoAppointmentHeader.autoUpdateStatusColor = '',pracStatusMaster.bgColour,appoAppointmentHeader.autoUpdateStatusColor)
END AS statusBGColour , pracStatusMaster.fontColour AS statusFontColour,
DATE_FORMAT(creationDate,'%Y-%m-%d %H:%i') AS creationDate,
fnc_GetAppoProviderResourceName(appoAppointmentProcedureSteps.appoHdrID,5,1) AS providerResource,
appoAppointmentResources.appoPositions,
fnc_GetAppointmentProviderResourceName(appoAppointmentProcedureSteps.appoHdrID,2,'N',1) AS providerName,
fnc_GetAppointmentProviderResourceName(appoAppointmentProcedureSteps.appoHdrID,3,'N',1) AS resourceName,
fnc_GetAppointmentProviderResourceNameByPercentage(appoAppointmentProcedureSteps.appoHdrID,2,'N',1) AS modifyproviderName,
fnc_GetAppointmentProviderResourceNameByPercentage(appoAppointmentProcedureSteps.appoHdrID,3,'N',1) AS modifyresourceName,
tpAppointmentPlan.appointmentPlanID, tpAppointmentPlan.number,pracStatusMaster.statusType,
ptntPatientDetails.workPhone,ptntPatientDetails.cellPhone,ptntPatientDetails.primaryPhone,
COALESCE(ptntPatientDetails.patientFName,'')patientFName,COALESCE(ptntPatientDetails.patientLName,'')patientLName,
COALESCE(ptntPatientDetails.patientNickName,'')patientNickName,ptntPatientDetails.patientID,
ROUND(appoTypeMaster.appoTypeUnit/5.00) AS appoTypeUnit
,appoAppointmentHeader.officeID,appoAppointmentHeader.createBy,pracOffice.practiceName AS practiceName,IF( ptntPatientDetails.patientID =5001,0,fnc_GetRecallFlag(ptntPatientDetails.patientID)) AS recallFlag,
ptntPatientDetails.officeID AS ptntofficeID,
appoAppointmentHeader.desiredDate,
appoAppointmentHeader.priority,
appoAppointmentHeader.isSCL,
'' AS providerCode,
pracResourceMaster.shortCode,
appoAppointmentHeader.appoUnscheduledCreatedFromID,
appoAppointmentHeader.clipBoardCreationDate,
appoAppointmentHeader.SCLCreationDate,
ptntPatientDetails.patientCode
FROM 
appoAppointmentHeader
INNER JOIN appoAppointmentHeaderLang ON appoAppointmentHeaderLang.appoHdrID=appoAppointmentHeader.hdrID AND appoAppointmentHeaderLang.langLangID=1
INNER JOIN appoAppointmentProcedureSteps ON appoAppointmentProcedureSteps.appoHdrID=appoAppointmentHeader.hdrID
INNER JOIN appoAppointmentResources ON appoAppointmentResources.appoHdrID =appoAppointmentHeader.hdrID
INNER JOIN pracResourceMaster ON pracResourceMaster.resourceID=appoAppointmentResources.pracResourceID
INNER JOIN pracResourceTypeMaster ON pracResourceTypeMaster.resourceTypeID=pracResourceMaster.resourceTypeID
INNER JOIN pracStatusMaster ON pracStatusMaster.statusID=appoAppointmentHeader.currentAppoHdrStatusID
INNER JOIN pracStatusMasterLang ON pracStatusMasterLang.pracStatusID=pracStatusMaster.statusID AND pracStatusMasterLang.langLangID = 1
INNER JOIN ptntPatientDetails ON ptntPatientDetails.patientID=appoAppointmentHeader.ptntPatientID AND ptntPatientDetails.isDeleted=0 AND ptntPatientDetails.patientID<>5001
LEFT OUTER JOIN tpAppointmentPlan ON appoAppointmentHeader.appoPlanID = tpAppointmentPlan.appointmentPlanID
LEFT OUTER JOIN pracOffice ON pracOffice.officeID=appoAppointmentHeader.officeID
LEFT OUTER JOIN appoUnscheduledCreatedFrom ON   appoUnscheduledCreatedFrom.appoUnscheduledCreatedFromID = appoAppointmentHeader.appoUnscheduledCreatedFromID
LEFT OUTER JOIN appoTypeMaster ON appoTypeMaster.typeID=appoAppointmentHeader.appoTypeID
LEFT OUTER JOIN appoTypeMasterLang ON appoTypeMasterLang.appoTypeID=appoTypeMaster.typeID
WHERE
(
  appoAppointmentHeader.currentAppoHdrStatusID=100 
OR
  (
    appoAppointmentHeader.appoDate >= fnc_getOrgCurDate()
    AND  FIND_IN_SET(appoAppointmentHeader.currentAppoHdrStatusID,'700,710,716,711,712,713,714,715,717,5002,5004,5005,5013,5014,6004,6005')>0   
    AND appoAppointmentHeader.isSCL='1'
  )
  OR
  (
       appoAppointmentHeader.currentAppoHdrStatusID=200
       AND appoAppointmentHeader.isSCL='1'
  ) )
)m ORDER BY m.clipBoardCreationDate DESC, m.hdrID DESC;

EXPLAIN returns below results

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	ALL	\N	\N	\N	\N	1966	100.00	Using filesort
2	DERIVED	ptntPatientDetails	ref	PRIMARY,isDeleted	isDeleted	1		842	100.00	Using where
2	DERIVED	appoAppointmentHeader	ref	PRIMARY,fkAppoHeader1,fkAppoHeader3,currentAppoHdrStatusID,hdrID	fkAppoHeader1	8	ptntPatientDetails.patientID	5	100.00	Using where
2	DERIVED	appoTypeMaster	eq_ref	PRIMARY	PRIMARY	4	appoAppointmentHeader.appoTypeID	1	100.00	
2	DERIVED	appoTypeMasterLang	ref	PRIMARY	PRIMARY	4	appoAppointmentHeader.appoTypeID	1	100.00	Using index
2	DERIVED	pracStatusMaster	eq_ref	PRIMARY	PRIMARY	4	appoAppointmentHeader.currentAppoHdrStatusID	1	100.00	
2	DERIVED	appoAppointmentHeaderLang	ref	PRIMARY	PRIMARY	8	appoAppointmentHeader.hdrID	1	100.00	
2	DERIVED	pracStatusMasterLang	eq_ref	PRIMARY	PRIMARY	8	appoAppointmentHeader.currentAppoHdrStatusID	1	100.00	
2	DERIVED	tpAppointmentPlan	eq_ref	PRIMARY,appointmentPlanID	PRIMARY	8	appoAppointmentHeader.appoPlanID	1	100.00	
2	DERIVED	pracOffice	eq_ref	PRIMARY,officeID	PRIMARY	4	appoAppointmentHeader.officeID	1	100.00	
2	DERIVED	appoUnscheduledCreatedFrom	eq_ref	PRIMARY	PRIMARY	4	appoAppointmentHeader.appoUnscheduledCreatedFromID	1	100.00	Using index
2	DERIVED	appoAppointmentProcedureSteps	eq_ref	PRIMARY	PRIMARY	8	appoAppointmentHeaderLang.appoHdrID	1	100.00	Using where
2	DERIVED	appoAppointmentResources	ref	PRIMARY,appoHdrID_pracResourceID_pracProviderID	PRIMARY	8	appoAppointmentProcedureSteps.appoHdrID	1	100.00	Using where
2	DERIVED	pracProviderToProviderTypeMap	ref	PRIMARY	PRIMARY	4	appoAppointmentResources.pracProviderID	1	100.00	Using index
2	DERIVED	pracProviderMaster	eq_ref	PRIMARY,providerID_isDeleted_providerStatus	PRIMARY	4	pracProviderToProviderTypeMap.pracProviderID	1	100.00	Using where
3	UNION	ptntPatientDetails	ref	PRIMARY,isDeleted	isDeleted	1		842	100.00	Using where
3	UNION	appoAppointmentHeader	ref	PRIMARY,fkAppoHeader1,currentAppoHdrStatusID,hdrID	fkAppoHeader1	8	ptntPatientDetails.patientID	5	100.00	Using where
3	UNION	pracStatusMasterLang	eq_ref	PRIMARY	PRIMARY	8	appoAppointmentHeader.currentAppoHdrStatusID	1	100.00	
3	UNION	appoAppointmentHeaderLang	eq_ref	PRIMARY	PRIMARY	12	appoAppointmentHeader.hdrID	1	100.00	
3	UNION	pracStatusMaster	eq_ref	PRIMARY	PRIMARY	4	appoAppointmentHeader.currentAppoHdrStatusID	1	100.00	
3	UNION	tpAppointmentPlan	eq_ref	PRIMARY,appointmentPlanID	PRIMARY	8	appoAppointmentHeader.appoPlanID	1	100.00	
3	UNION	pracOffice	eq_ref	PRIMARY,officeID	PRIMARY	4	appoAppointmentHeader.officeID	1	100.00	
3	UNION	appoUnscheduledCreatedFrom	eq_ref	PRIMARY	PRIMARY	4	appoAppointmentHeader.appoUnscheduledCreatedFromID	1	100.00	Using index
3	UNION	appoTypeMaster	eq_ref	PRIMARY	PRIMARY	4	appoAppointmentHeader.appoTypeID	1	100.00	
3	UNION	appoTypeMasterLang	ref	PRIMARY	PRIMARY	4	appoTypeMaster.typeID	1	100.00	Using index
3	UNION	appoAppointmentProcedureSteps	eq_ref	PRIMARY	PRIMARY	8	appoAppointmentHeader.hdrID	1	100.00	
3	UNION	appoAppointmentResources	ref	PRIMARY,appoHdrID_pracResourceID_pracProviderID	PRIMARY	8	appoAppointmentProcedureSteps.appoHdrID	1	100.00	Using where
3	UNION	pracResourceTypeMaster	index	PRIMARY	PRIMARY	4	\N	4	100.00	Using index; Using join buffer
3	UNION	pracResourceMaster	eq_ref	PRIMARY,fkResourceMaster	PRIMARY	4	appoAppointmentResources.pracResourceID	1	100.00	Using where
\N	UNION RESULT	<union2,3>	ALL	\N	\N	\N	\N	\N	\N