Results 1 to 14 of 14

Thread: Optimized Query

  1. #1
    Join Date
    Dec 2010
    Posts
    11

    Unanswered: Optimized Query

    Hi ALL,

    When i run this query it's take too much time to execute.


    SELECT ptnt.*,ptntF.familyName FROM
    -> (SELECT ptntPatientDetails.*,ptntPatientDetailsLang.occupa tion,ptntPatientDetailsLang.notes,pracStatusMaster Lang.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,pracStat usMasterLang
    -> 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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first thing you need to do is reformat your SQL so that it becomes easier to understand
    Code:
    SELECT ptnt.*
         , ptntF.familyName 
      FROM ( SELECT ptntPatientDetails.*
                  , ptntPatientDetailsLang.occupa tion
                  , ptntPatientDetailsLang.notes
                  , pracStatusMaster Lang.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;
    now that's better, don't you think?

    first suggestion is to use explicit JOIN sytntax

    second suggestion is to remove the correlated subqueries for country and state

    third suggestion is to pop the first derived table up to the outer query

    your second derived table (ptntF) is fine

    Code:
    SELECT ptntPatientDetails.*
         , ptntPatientDetailsLang.occupation
         , ptntPatientDetailsLang.notes
         , pracStatusMasterLang.statusDesc
         , COALESCE(ice_global.genCountry.countryName,'') as countryName
         , COALESCE(ice_global.genState.stateName,'') as stateName
         , ptntF.familyName 
      FROM ptntPatientDetails
    INNER
      JOIN ptntPatientDetailsLang
        ON ptntPatientDetailsLang.ptntPatientID = ptntPatientDetails.patientID
       AND ptntPatientDetailsLang.langLangID = 1
    INNER
      JOIN pracStatusMasterLang
        ON pracStatusMasterLang.pracStatusID = ptntPatientDetails.ptntStatus
    LEFT OUTER
      JOIN ice_global.genCountry.countryID 
        ON ice_global.genCountry.countryID = ptntPatientDetails.country
    LEFT OUTER
      JOIN ice_global.genState.stateID
        ON ice_global.genState.stateID = ptntPatientDetails.state
     WHERE ptntPatientDetails.isDeleted = 0
       AND ptntPatientDetails.patientCode IS NOT NULL 
    LEFT 
      JOIN ( SELECT GROUP_CONCAT(ptntFamilyMasterLang.familyName) as familyName
                  , ptntFamilyPatients.ptntPatientID
               FROM ptntFamilyMasterLang
             INNER
               JOIN ptntFamilyPatients
                 ON ptntFamilyPatients.ptntFamilyID = ptntFamilyMasterLang.ptntFamilyID
              WHERE ptntFamilyMasterLang.langLangID = 1
             GROUP 
                 BY ptntFamilyPatients.ptntPatientID ) ptntF
        ON ptntF.ptntPatientID = ptntPatientDetails.patientID
    could you first of all confirm that this gives the same results, and second, how is the performance?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    11
    Thanks! Your suggestions really help me to solve my problem. I make some changes
    SELECT ptntPatientDetails.*
    , ptntPatientDetailsLang.occupation
    , ptntPatientDetailsLang.notes
    , pracStatusMasterLang.statusDesc
    , COALESCE(ice_global.genCountry.countryName,'') as countryName
    , COALESCE(ice_global.genState.stateName,'') as stateName
    , ptntF.familyName
    FROM ptntPatientDetails
    INNER
    JOIN ptntPatientDetailsLang
    ON ptntPatientDetailsLang.ptntPatientID = ptntPatientDetails.patientID
    AND ptntPatientDetailsLang.langLangID = 1
    INNER
    JOIN pracStatusMasterLang
    ON pracStatusMasterLang.pracStatusID = ptntPatientDetails.ptntStatus
    LEFT OUTER
    JOIN ice_global.genCountry
    ON ice_global.genCountry.countryID = ptntPatientDetails.country
    LEFT OUTER
    JOIN ice_global.genState
    ON ice_global.genState.stateID = ptntPatientDetails.state
    LEFT
    JOIN ( SELECT GROUP_CONCAT(ptntFamilyMasterLang.familyName) familyName
    , ptntFamilyPatients.ptntPatientID
    FROM ptntFamilyMasterLang
    INNER
    JOIN ptntFamilyPatients
    ON ptntFamilyPatients.ptntFamilyID = ptntFamilyMasterLang.ptntFamilyID
    WHERE ptntFamilyMasterLang.langLangID = 1
    GROUP
    BY ptntFamilyPatients.ptntPatientID ) ptntF
    ON ptntF.ptntPatientID = ptntPatientDetails.patientID
    WHERE ptntPatientDetails.isDeleted = 0
    AND ptntPatientDetails.patientCode IS NOT NULL

    It gives me the same results but execution time is around 34.28 sec.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vaskarbasak View Post
    It gives me the same results but execution time is around 34.28 sec.
    what indexes have you defined?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2010
    Posts
    11
    ptntPatientDetailsLang--

    Index coloums - ptntpatientID,langlangID
    Index Type - Btree
    Primary Key (ptntpatientID,langlangID)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's it????

    no indexes on the other tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2010
    Posts
    11
    ptntPatientDetails ---

    Index Coloums - patientID
    Index Type - BTREE


    ptntPatientDetailsLang--

    Index coloums - ptntpatientID,langlangID
    Index Type - Btree


    pracStatusMasterLang --

    Index Coloums - pracStatusID, langLangID
    Index Type - Btree


    ptntFamilyPatients --

    Index Coloums - ptntFamilyID,ptntPatientID
    Index Tyupe - Btree

    ptntFamilyMasterLang ---

    Index Coloums - ptntFamilyID,langLangID
    Index Tyupe - Btree

    genCountry--

    Index Coloums - countryID
    Index Tyupe - Btree

    genState--

    Index Coloums - stateID,genCountryID
    Index Tyupe - Btree

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for fixing my query (moving the WHERE clause to the end where it belongs)

    check each of your joins -- the columns involved should have a usable index

    if in doubt, do another EXPLAIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2010
    Posts
    11
    +----+-------------+------------------------+--------+---------------+---------+---------+----------------------------------------------------------+--------+----------------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------------------+--------+---------------+---------+---------+----------------------------------------------------------+--------+----------------------------------------------+
    | 1 | PRIMARY | ptntPatientDetailsLang | ALL | PRIMARY | NULL | NULL | NULL | 376596 | Using where |
    | 1 | PRIMARY | ptntPatientDetails | eq_ref | PRIMARY | PRIMARY | 8 | ice_local_staging_1.ptntPatientDetailsLang.ptntPat ientID | 1 | Using where |
    | 1 | PRIMARY | pracStatusMasterLang | ref | PRIMARY | PRIMARY | 4 | ice_local_staging_1.ptntPatientDetails.ptntStatus | 1 | Using where |
    | 1 | PRIMARY | genCountry | eq_ref | PRIMARY | PRIMARY | 4 | ice_local_staging_1.ptntPatientDetails.country | 1 | |
    | 1 | PRIMARY | genState | ref | PRIMARY | PRIMARY | 4 | ice_local_staging_1.ptntPatientDetails.state | 1 | |
    | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 31 | |
    | 2 | DERIVED | ptntFamilyMasterLang | ALL | PRIMARY | NULL | NULL | NULL | 21 | Using where; Using temporary; Using filesort |
    | 2 | DERIVED | ptntFamilyPatients | ref | PRIMARY | PRIMARY | 4 | ice_local_staging_1.ptntFamilyMasterLang.ptntFamil yID | 1 | Using index |
    +----+-------------+------------------------+--------+---------------+---------+---------+----------------------------------------------------------+--------+----------------------------------------------+


    Explain give above results.Any suggestions for me

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vaskarbasak View Post
    Any suggestions for me
    did you add the indexes i suggested?

    f.y.i. the EXPLAIN is a ~lot~ easier to read if you format it inside tags
    Code:
    +----+-------------+------------------------+--------+---------------+---------+---------+----------------------------------------------------------+--------+----------------------------------------------+
    | id | select_type | table                  | type   | possible_keys | key     | key_len | ref                                                      | rows   | Extra                                        |
    +----+-------------+------------------------+--------+---------------+---------+---------+----------------------------------------------------------+--------+----------------------------------------------+
    |  1 | PRIMARY     | ptntPatientDetailsLang | ALL    | PRIMARY       | NULL    | NULL    | NULL                                                     | 376596 | Using where                                  |
    |  1 | PRIMARY     | ptntPatientDetails     | eq_ref | PRIMARY       | PRIMARY | 8       | ice_local_staging_1.ptntPatientDetailsLang.ptntPatientID |      1 | Using where                                  |
    |  1 | PRIMARY     | pracStatusMasterLang   | ref    | PRIMARY       | PRIMARY | 4       | ice_local_staging_1.ptntPatientDetails.ptntStatus        |      1 | Using where                                  |
    |  1 | PRIMARY     | genCountry             | eq_ref | PRIMARY       | PRIMARY | 4       | ice_local_staging_1.ptntPatientDetails.country           |      1 |                                              |
    |  1 | PRIMARY     | genState               | ref    | PRIMARY       | PRIMARY | 4       | ice_local_staging_1.ptntPatientDetails.state             |      1 |                                              |
    |  1 | PRIMARY     | <derived2>             | ALL    | NULL          | NULL    | NULL    | NULL                                                     |     31 |                                              |
    |  2 | DERIVED     | ptntFamilyMasterLang   | ALL    | PRIMARY       | NULL    | NULL    | NULL                                                     |     21 | Using where; Using temporary; Using filesort |
    |  2 | DERIVED     | ptntFamilyPatients     | ref    | PRIMARY       | PRIMARY | 4       | ice_local_staging_1.ptntFamilyMasterLang.ptntFamilyID    |      1 | Using index                                  |
    +----+-------------+------------------------+--------+---------------+---------+---------+----------------------------------------------------------+--------+----------------------------------------------+
    notice some steps do ~not~ use an index

    it looks like the ptntPatientDetailsLang is undergoing a table scan

    could you please do this --
    Code:
    SHOW CREATE TABLE ptntPatientDetailsLang
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2010
    Posts
    11
    ptntPatientDetailsLang | CREATE TABLE `ptntPatientDetailsLang` (
    `ptntPatientID` bigint(20) NOT NULL,
    `langLangID` int(11) NOT NULL,
    `notes` varchar(1000) collate utf8_bin default NULL,
    `occupation` varchar(100) collate utf8_bin default NULL,
    PRIMARY KEY (`ptntPatientID`,`langLangID`),
    CONSTRAINT `fk_ptntPatientDetailsLang_ptntPatientID` FOREIGN KEY (`ptntPatientID`) REFERENCES `ptntPatientDetails` (`patientID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

    any idea where i add the index? Please help me.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm not saying this is going to improve the performance of the query, but you can try this --

    ALTER TABLE ptntPatientDetailsLang
    ADD INDEX (langLangID,ptntPatientID)

    you might also try --

    ALTER TABLE ptntPatientDetails
    ADD INDEX (isDeleted,patientCode)

    however, i'm not very hopeful about either of these

    i mean, let's face it, you do seem to be dumping a very large part of these tables

    are you really going to use all quarter million result rows?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Dec 2010
    Posts
    11

    otrato Onerating

    I have applied your suggestion .But it don’t improve performance.

    Yes I am using quarter million or more result rows.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vaskarbasak View Post
    Yes I am using quarter million or more result rows.
    in that case, 35 seconds seems ~very~ reasonable
    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
  •