mysql group by date with multiple join

Question

SELECT 
    tba.UpdatedDate AS  UpdatedDate,
    tsh.SupplierID,
    ts.ProductCode  as ProductCode,
    sum(tba.AfterDiscount) as AfterDiscount,
    sum(tba.Quantity) as Quantity 
FROM 
    tblstockhistory as tsh
    left join tblstock as ts 
    on tsh.StockID=ts.StockID 
    left join tblbasket as tba 
    on ts.ProductCode=tba.ProductCode 
        and tsh.SupplierID=49 
        AND tba.Status=3

group by 
    tba.UpdatedDate 
ORDER BY
    Quantity DESC

i have the supplier table, the supplier id tagged in to tblstockhistory table, and in this tblstockhistory table contains the StockID(reference from tblstock table), and i have Stock table contains StockID, ProductCode , And i have the tblbasket table , in this am maintaining the ProductCode,

My idea here , i want to show thw stats by supplierID, when i pass the supplier id, it show show , this supplier supplied goods sale stats,

But the above query sometime return null value, and it takes too much time for excution, around 50 seconds ,

I what somthing like below from above query

Date         SupplierID, Amount,  Quantity
2010-12-12      12      12200       20
2010-12-12      40      10252       30
2010-12-12      10      12551       50


2010-12-13      22      1900        20
2010-12-13      40      18652       30
2010-12-13      85      19681       50

2010-12-15      22      1900        20
2010-12-15      40      18652       30
2010-12-15      85      19681       50

to increase performance make sure that all fields you are using in joins are indexed. — Nazariy, Dec 13 '10 at 17:23

Conrad Frix · Answer 1 · 2010-12-14T16:51:48.040

1

Does a tblstockhistory ever exist without a stockID. If it doesn't you can convert it to an inner join which can help.

e.g.

tblstockhistory as tsh
INNER join tblstock as ts 
on tsh.StockID=ts.StockID

Also you might to consider adding indexes if they don't currently exist.

At the very least I would have the following fields indexed since they will likely be joined and queried commonly.

tblstockhistory.SockID
tblstockhistory.SupplierID
tblstock.StockID
tblstock.ProductCode
tblbasket.ProductCode
tblBacket.Status
tblbasket.UpdatedDate

Finally if its really important that this query be lightening fast you can create summary tables and update them periodically.

edited Dec 14 '10 at 16:51

answered Dec 13 '10 at 17:23

Conrad Frix

51,984
12
96
155

For which Fields i should create index here – Bharanikumar Dec 13 '10 at 17:24
StockID, is required, becoz with stockid only we are pull the ProductCode, ProductCode stored in tblbasket table, – Bharanikumar Dec 13 '10 at 17:25
Added the fields I would have indexed at a minimum. Also since StockID is required I would definitely change it to an Inner Join. You might also want to drop ProductCode from the Select since it doesn't appear to be on your sample output. – Conrad Frix Dec 13 '10 at 17:37
u r saying this line on ts.ProductCode=tba.ProductCode OR ts.ProductCode as ProductCode – Bharanikumar Dec 14 '10 at 05:01
I meant ts.ProductCode as ProductCode – Conrad Frix Dec 14 '10 at 16:52

score 0 · Answer 2 · answered Dec 13 '10 at 17:36

0

re write the group by clause as and try again

group by 
    tba.UpdatedDate, tsh.SupplierID

you have mentioned ProductCode in your query but not in the 'result' you wanted if you want to display ProductCode as well then add it to the group by clause or else remove it from the select clause.

answered Dec 13 '10 at 17:36

wizzardz

5,664
5
44
66

But this is [MySQL](http://stackoverflow.com/questions/1066453/mysql-group-by-and-order-by/1066504#1066504) – Dec 13 '10 at 17:57

mysql group by date with multiple join

2 Answers2