Hi ALL,
When i run this query it's take too much time to execute.
SELECT ptnt.*,ptntF.familyName FROM
-> (SELECT ptntPatientDetails.*,ptntPatientDetailsLang.occupation,ptntPatientDetailsLang.notes,pracStatusMasterLang.statusDesc,
-> IFNULL((select countryName from genCountry where ice_global.genCountry.countryID = ptntPatientDetails.country),'') as countryName,
-> IFNULL((select stateName from genState where ice_global.genState.stateID = ptntPatientDetails.state),'') as stateName
-> FROM ptntPatientDetails,ptntPatientDetailsLang,pracStatusMasterLang
-> WHERE ptntPatientDetails.patientID = ptntPatientDetailsLang.ptntPatientID
-> AND ptntPatientDetails.ptntStatus = pracStatusMasterLang.pracStatusID
-> AND ptntPatientDetailsLang.langLangID = 1
-> AND ptntPatientDetails.isDeleted = 0
-> AND ptntPatientDetails.patientCode IS NOT NULL
-> )
-> ptnt
-> LEFT JOIN
-> (SELECT GROUP_CONCAT(ptntFamilyMasterLang.familyName) as familyName, ptntFamilyPatients.ptntPatientID
-> FROM ptntFamilyMasterLang,ptntFamilyPatients
-> WHERE ptntFamilyMasterLang.langLangID=1
-> AND ptntFamilyPatients.ptntFamilyID = ptntFamilyMasterLang.ptntFamilyID
-> GROUP BY ptntFamilyPatients.ptntPatientID)
-> ptntF
-> ON ptnt.patientID = ptntF.ptntPatientID;
EXPLAIN give me the below result
+----+--------------------+------------------------+--------+---------------+---------+---------+----------------------------------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------------------+--------+---------------+---------+---------+----------------------------------------------------+--------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 408094 | |
| 1 | PRIMARY | <derived5> | ALL | NULL | NULL | NULL | NULL | 21 | |
| 5 | DERIVED | ptntFamilyMasterLang | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using where; Using temporary; Using filesort |
| 5 | DERIVED | ptntFamilyPatients | ref | PRIMARY | PRIMARY | 4 | ptntFamilyMasterLang.ptntFamilyID | 1 | Using index |
| 2 | DERIVED | ptntPatientDetailsLang | ALL | PRIMARY | NULL | NULL | NULL | 306457 | Using where |
| 2 | DERIVED | ptntPatientDetails | eq_ref | PRIMARY | PRIMARY | 8 | ptntPatientDetailsLang.ptntPatientID | 1 | Using where |
| 2 | DERIVED | pracStatusMasterLang | ref | PRIMARY | PRIMARY | 4 | ptntPatientDetails.ptntStatus | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | genState | ref | PRIMARY | PRIMARY | 4 | ptntPatientDetails.state | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | genCountry | eq_ref | PRIMARY | PRIMARY | 4 | ptntPatientDetails.country | 1 | Using where |
+----+--------------------+------------------------+--------+---------------+---------+---------+----------------------------------------------------+-------
any one please help me to solve my problem.
Thanks!