Senthilkumar Posted July 1 Share Posted July 1 Dear Team, I have the below query to get my output SELECT c.branchName as Branch,a.CustomerID as CustomerID, a.CustomerName, b.VisitInterval, b.VisitPlan, b.VisitType, d.Fname as Name, e.deptName as Department FROM sbms.machinemaster as a LEFT JOIN sbms.customerassign as b ON b.RowID = a.id INNER JOIN sbms.branch as c ON c.branchcode = a.BranchCode LEFT JOIN sbms.usertable AS d ON d.EmpNo = b.EmpID LEFT JOIN sbms.deparment AS e ON e.deptID = d.Department WHERE a.MachineStatus = 'A' AND c.branchcode NOT IN ('EXP','HO') GROUP BY a.BranchCode, a.CustomerID order by Branch When I am using the above query it takes 62 seconds to execute. But I have another query which is the same as the above SELECT c.branchName as Branch,a.CustomerID as CustomerID, a.CustomerName, b.VisitInterval, b.VisitPlan, b.VisitType, d.Fname as Name, e.deptName as Department FROM sbms.machinemaster as a LEFT JOIN sbms.customerassign as b ON b.CustomerID = a.CustomerID INNER JOIN sbms.branch as c ON c.branchcode = a.BranchCode LEFT JOIN sbms.usertable AS d ON d.EmpNo = b.EmpID LEFT JOIN sbms.deparment AS e ON e.deptID = d.Department WHERE a.MachineStatus = 'A' AND c.branchcode NOT IN ('EXP','HO') GROUP BY a.BranchCode, a.CustomerID order by Branch This query is executing within 2 seconds. The only difference between both the queries is b.RowID = a.id and b.CustomerID = a.CustomerID on my left join. I create the index for all my join columns. can anyone please suggest why the first query takes a long time to execute? I uploaded my table dump here for your reference. https://drive.google.com/file/d/1zQTJV-2ppQPjyEdxtOwAh_EVnOaQ-TOO/view?usp=sharing Quote Link to comment Share on other sites More sharing options...
requinix Posted July 1 Share Posted July 1 Post the EXPLAIN output of your query. EXPLAIN SELECT c.branchName as Branch, ... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.