Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2010
    Posts
    11

    Unanswered: optimized query-help

    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;"

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vaskarbasak View Post
    I am using this querry which works fine;
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    11

    obtimized query-help

    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 | |
    +----+-------------+--------------------------------------+--------+-----------------------------------------------------------------------------------------+----------------------------------+---------+--------------------------------------------------------------------------------------------------------+-------+---------------------------------+

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's a different query, isn't it

    i'm sorry, both of those queries are way too complicated for me to understand
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2010
    Posts
    11
    I have modified my previous query but still it is taking lot of time to execute.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you write these queries?

    is there nobody in your organization who can help you? some database administrator?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2010
    Posts
    11
    yes i wrote it.But it takes lot of time.Please help.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vaskarbasak View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •