MySQL Forums
Forum List  »  Optimizer & Parser

Re: optimize query -- need help
Posted by: Rick James
Date: October 16, 2011 10:58AM

If I recall correctly, 7.3 will "push down joins"; this should improve JOINs significantly.

Select  A.* , B.* , C.* ,SUM(D.Length_Of_Training*D.Training_Fee) as Training_Fee ,
        SUM(D.Length_Of_Training*D.Total_Training_Salary) as Total_Training_Salary ,
        SUM(D.Length_Of_Employment*D.Total_Employment_Salary) as Total_Employment_Salary
    FROM  cbp_pcetcontract A , cbp_contractdeliverable D,
        cbp_contract E , cbp_position C ,
        cbp_bpprofile B
    where  D.Contract_ID = E.Contract_ID
      and  A.ContractDeliverableId = D.CD_Id
      and  A.PositionId = C.Position_ID
      and  B.BP_Id = A.BP_Id
Eh? SUMs and other fields without any GROUP BY??
Eh? You pull in E, but don't use any of its fields??

Try this on both MyISAM and NDB:
 
Select  A.* , B.* , C.* , D.*
    FROM  
      ( SELECT  CD_Id, 
                SUM(Length_Of_Training * Training_Fee) as Training_Fee ,
                SUM(Length_Of_Training * Total_Training_Salary) as Total_Training_Salary ,
                SUM(Length_Of_Employment * Total_Employment_Salary) as Total_Employment_Salary
            FROM  cbp_contractdeliverable
      ) D,
        cbp_pcetcontract A ,
        cbp_position C ,
        cbp_bpprofile B
    where  A.ContractDeliverableId = D.CD_Id
      and  A.PositionId = C.Position_ID
      and  B.BP_Id = A.BP_Id

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: optimize query -- need help
747
October 16, 2011 10:58AM
740
October 18, 2011 10:42PM


Sorry, you can't reply to this topic. It has been closed.

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.