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!

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.