0

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!

Edited by vaskar: n/a

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by smantscheff
0

See to it that you have indexes on all relevant fields.
Show your "CREATE TABLE" statements so that we can see which are missing.
Is there an index on ptntPatientDetailsLang.ptntPatientID ? If not, add it.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.