MySQL Forums
Forum List  »  Optimizer & Parser

Re: "Optimizing sub queries with joins"
Posted by: Rick James
Date: December 05, 2010 12:45PM

SELECT  T1.PATIENT_ID,T1.PATIENT_PAID AS VP_PAT,
        T2.PAID AS LD_PAT,T1.STATUS
    FROM  (
              ( SELECT  PROCEDURE_ID, PATIENT_PAID, PATIENT_ID, STATUS
                    FROM  VISIT_PROCEDURE
                    WHERE  CLINIC_ID = 97846
              ) AS T1
            LEFT JOIN  
              ( SELECT  PROCEDURE_ID, SUM(AMOUNT) AS PAID
                    FROM  LEDGER
                    WHERE  TYPE IN(5,8)
                      AND  CURR_PARTY = 4
                      AND  CLINIC_ID = 97846
                    GROUP BY  PROCEDURE_ID
              ) AS T2 ON T1.PROCEDURE_ID = T2.PROCEDURE_ID
           )
    WHERE  ROUND(T1.PATIENT_PAID,1) <> ROUND(T2.PAID, 1)

* If a subquery is doing something to diminish the number of rows (GROUP BY, LIMIT, DISTINCT, etc), keep it as a subquery. (You have a GROUP BY on the second subquery)
* Otherwise turn it into a JOIN, but make sure there is a good index. VISIT_PROCEDURE needs INDEX(CLINIC_ID, PROCEDURE_ID).
* Check remaining subqueries for having optimal indexes. LEDGER needs INDEX(CLINIC_ID, CURR_PARTY, TYPE).
* The subqueries will be performed first, and create temp tables with no indexes. In your case (fortunately) there is only one subquery.
* You said "LEFT" JOIN. Was that deliberate, or accidental? It may be throwing a monkey wrench into optimizing, forcing the JOIN to do table scans of the unindexed subquery. Please run EXPLAIN on this to see which table it starts with:

SELECT  T1.PATIENT_ID,
        T1.PATIENT_PAID AS VP_PAT,
        T2.PAID AS LD_PAT,
        T1.STATUS
    FROM  VISIT_PROCEDURE T1
    LEFT JOIN  
              ( SELECT  PROCEDURE_ID, SUM(AMOUNT) AS PAID
                    FROM  LEDGER
                    WHERE  TYPE IN(5,8)
                      AND  CURR_PARTY = 4
                      AND  CLINIC_ID = 97846
                    GROUP BY  PROCEDURE_ID
              ) AS T2  ON T1.PROCEDURE_ID = T2.PROCEDURE_ID
    WHERE  ROUND(T1.PATIENT_PAID,1) <> ROUND(T2.PAID, 1) 
      AND  T1.CLINIC_ID = 97846

If that does not perform well, come back and I will provide you with a messier (but faster) solution.

Options: ReplyQuote


Subject
Views
Written By
Posted
3084
December 01, 2010 10:42PM
Re: "Optimizing sub queries with joins"
1463
December 05, 2010 12:45PM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.