MySQL Forums
Forum List  »  Knowledge Base

Left joing is taking too much time
Posted by: mahesh badgujar
Date: August 24, 2009 03:44AM

Hi ,

I have a following query that uses a left join.

select bs.dealerid,bs.dealername, bs.productid,bs.productname,bs.date,plm.date, plm.planned as planned, plm.actual as actual
from
(
select dm.dealerid, dm.dealername, pm.productid, pm.productname, dt.date from icmis.dealer_master dm,
icmis.product_master pm, icmis.datetable dt where dm.dealerid in(select eat.dealerid from icmis.employee_allocation_t eat

where eat.employeeid=1) and (date(dt.date) >= '2007-04-01' and date(dt.date) <= '2008-03-01')
) bs

left join

(
select mstr.dealerid, mstr.productid, mstr.date, sum(mstr.planned) planned,sum(mstr.actual) actual
from
(
select plm.dealerid, plm.productid, plm.date, plm.value planned, 0 as actual from icmis.planning_master plm
where plm.dealerid in(select eat.dealerid from icmis.employee_allocation_t eat where eat.employeeid=1)
union
select ae.dealerid, ae.productid, ae.date, 0 as planned, ae.value as actual from icmis.actual_entry ae where ae.dealerid
in (select eat.dealerid from icmis.employee_allocation_t eat where eat.employeeid=1)
) mstr group by mstr.dealerid,mstr.productid, mstr.date

) plm on (bs.date = plm.date and bs.dealerid = plm.dealerid and bs.productid = plm.productid) order by 1,5


This query returns me 22000 records in result. And it takes near about 7 sec.
I want option or modification in this query that gives me same result with
improvement in performance... Pls help me asap..
As per my knowledge left join is creating a problem here..

Options: ReplyQuote


Subject
Views
Written By
Posted
Left joing is taking too much time
2466
August 24, 2009 03:44AM


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.