Hi All,

I am using this querry which works fine;

Please advise me to optimized query

"SELECT ptntPatientDetails.patientID,
CF.CustomField,
CF.FiledTitle,
CF.fieldValue
FROM ptntPatientDetails
LEFT JOIN
(
SELECT customFieldMaster.customFieldID AS CustomField,
customFieldMasterLang.customFieldTitle AS FiledTitle,
GROUP_CONCAT(CAST(IF((customFieldMaster.fieldTypeI D = 1 OR customFieldMaster.fieldTypeID = 2),s.fieldDetailsValue,
COALESCE(IF((s.isChecked=1),customFieldTypeDetails .fieldOptionName,NULL))) AS CHAR)) AS fieldValue,
s.ptntID
FROM
customFieldMaster
INNER JOIN customFieldMasterLang
ON customFieldMaster.customFieldID = customFieldMasterLang.customFieldID
INNER JOIN customFieldTypeDetails
ON customFieldMaster.customFieldID = customFieldTypeDetails.customFieldID
LEFT JOIN
(
SELECT DISTINCT(ptntPatientToCustomFieldMatchDetails.fiel dDetailsID),
ptntPatientToCustomFieldMatchDetails.fieldDetailsV alue,
ptntPatientToCustomFieldMatchDetails.isChecked,
ptntPatientDetails.patientID AS ptntID,
ptntPatientToCustomFieldMatch.customFieldID
FROM
ptntPatientDetails
LEFT JOIN ptntPatientToCustomFieldMatch
ON ptntPatientDetails.patientID = ptntPatientToCustomFieldMatch.patientID
LEFT JOIN ptntPatientToCustomFieldMatchDetails
ON ptntPatientToCustomFieldMatch.ID = ptntPatientToCustomFieldMatchDetails.ID
) s
ON customFieldTypeDetails.fieldDetailsID = s.fieldDetailsID
AND customFieldTypeDetails.customFieldID = s.customFieldID
WHERE customFieldMaster.statusID = 1
AND customFieldMaster.isDeleted = 0
AND customFieldTypeDetails.isDeleted = 0
AND customFieldMasterLang.langID = 1
AND s.ptntID IS NOT NULL
GROUP BY s.ptntID,customFieldMaster.customFieldID
) CF
ON ptntPatientDetails.patientID = CF.ptntID
WHERE ptntPatientDetails.isDeleted = 0
AND ptntPatientDetails.patientCode IS NOT NULL;"

if it works fine, why do you think it needs to be "optimized"?

please do a SHOW CREATE TABLE for every table involved in the query, and please post the EXPLAIN for this query

Hi All,

I am using this querry which works fine;

Please advise me to optimized query


SELECT ptntPatientDetails.patientID,
CF.CustomField,
CF.FiledTitle,
CF.fieldValue
FROM ptntPatientDetails
LEFT JOIN
(

SELECT ptntPatientToCustomFieldMatch.patientID,customFiel dMaster.customFieldID AS CustomField,
customFieldMasterLang.customFieldTitle AS FiledTitle,
GROUP_CONCAT(CAST(IF((customFieldMaster.fieldTypeI D = 1 OR customFieldMaster.fieldTypeID = 2),
ptntPatientToCustomFieldMatchDetails.fieldDetailsV alue,
COALESCE(IF((ptntPatientToCustomFieldMatchDetails. isChecked=1),
customFieldTypeDetails.fieldOptionName,NULL))) AS CHAR)) AS fieldValue
FROM
customFieldMaster
INNER JOIN customFieldMasterLang
ON customFieldMaster.customFieldID = customFieldMasterLang.customFieldID

INNER JOIN customFieldTypeDetails
ON customFieldMaster.customFieldID = customFieldTypeDetails.customFieldID

LEFT JOIN ptntPatientToCustomFieldMatch
ON customFieldTypeDetails.customFieldID = ptntPatientToCustomFieldMatch.customFieldID

LEFT JOIN ptntPatientToCustomFieldMatchDetails
ON (customFieldTypeDetails.fieldDetailsID = ptntPatientToCustomFieldMatchDetails.fieldDetailsI D
AND ptntPatientToCustomFieldMatch.ID = ptntPatientToCustomFieldMatchDetails.ID)

WHERE customFieldMaster.statusID = 1
AND customFieldMaster.isDeleted = 0
AND customFieldTypeDetails.isDeleted = 0
AND ptntPatientToCustomFieldMatch.patientID IS NOT NULL
GROUP BY ptntPatientToCustomFieldMatch.patientID,customFiel dMaster.customFieldID
)CF
ON ptntPatientDetails.patientID = CF.patientID
AND ptntPatientDetails.isDeleted = 0
AND ptntPatientDetails.patientCode IS NOT NULL;


EXPLAIN
-----------------------
+----+-------------+--------------------------------------+--------+-----------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------------------------------------------------------------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------------------+--------+-----------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------------------------------------------------------------------------------+-------+---------------------------------+
| 1 | PRIMARY | ptntPatientDetails | ALL | NULL | NULL | NULL | NULL | 6160 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 12012 | |
| 2 | DERIVED | customFieldMasterLang | ALL | PRIMARY,fkcustomFieldMasterLang1 | NULL | NULL | NULL | 4 | Using temporary; Using filesort |
| 2 | DERIVED | customFieldTypeDetails | ref | fkcustomFieldTypeDetails1 | fkcustomFieldTypeDetails1 | 4 | essentialdental.customFieldMasterLang.customFieldI D | 4 | Using where |
| 2 | DERIVED | customFieldMaster | eq_ref | PRIMARY | PRIMARY | 4 | essentialdental.customFieldTypeDetails.customField ID | 1 | Using where |
| 2 | DERIVED | ptntPatientToCustomFieldMatch | ref | fkptntPatientToCustomFieldMatch1,fkptntPatientToCu stomFieldMatch2 | fkptntPatientToCustomFieldMatch2 | 4 | essentialdental.customFieldTypeDetails.customField ID | 1916 | Using where; Using index |
| 2 | DERIVED | ptntPatientToCustomFieldMatchDetails | eq_ref | PRIMARY,fkptntPatientToCustomFieldMatchDetails1,fk ptntPatientToCustomFieldMatchDetails2 | PRIMARY | 12 | essentialdental.ptntPatientToCustomFieldMatch.ID,e ssentialdental.customFieldTypeDetails.fieldDetails ID | 1 | |
+----+-------------+--------------------------------------+--------+-----------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------------------------------------------------------------------------------+-------+---------------------------------+

that's a different query, isn't it

i'm sorry, both of those queries are way too complicated for me to understand

I have modified my previous query but still it is taking lot of time to execute.

did you write these queries?

is there nobody in your organization who can help you? some database administrator?

yes i wrote it.But it takes lot of time.Please help.

i can't

i don't understand the queries, and i don't know your tables or indexes

the solution will be in your indexes, by the way