Optimized Query

vaskarbasak · #1 (**permalink**) 12-10-10, 09:13

r937 · #2 (**permalink**) 12-10-10, 11:11

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?

vaskarbasak · #3 (**permalink**) 12-13-10, 01:31

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.

r937 · #4 (**permalink**) 12-13-10, 01:57

Quote:

Originally Posted by vaskarbasak

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

what indexes have you defined?

vaskarbasak · #5 (**permalink**) 12-13-10, 05:19

ptntPatientDetailsLang--

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

r937 · #6 (**permalink**) 12-13-10, 06:15

that's it????

no indexes on the other tables?

vaskarbasak · #7 (**permalink**) 12-13-10, 06:38

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

r937 · #8 (**permalink**) 12-13-10, 13:31

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

vaskarbasak · #9 (**permalink**) 12-13-10, 23:30

r937 · #10 (**permalink**) 12-13-10, 23:51

Quote:

Originally Posted by vaskarbasak

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

vaskarbasak · #11 (**permalink**) 12-14-10, 00:19

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.

r937 · #12 (**permalink**) 12-14-10, 05:59

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?