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