Hi,

I need help improving my query. I would be happy if some will tell me what am I doing wrong??

here is the query:
SELECT DISTINCT /* Traffic source */ Traffic.tsname as Traffic_Source, Traffic.tsid as TsID, /* Banner data */ TempImp.BannerName as BannerName, TempImp.BannerSize as BannerSize, TempImp.BannerType as BannerType, /* General*/

(COALESCE(TempImp.impCount, 0)) as NumOfImpressions,

(TempClicks.clicks) as NumberOfClicks,

ROUND((TempClicks.UnpaidClickCom), 3) as UnpaidClicksCommission,

ROUND((TempClicks.paidClickCom), 3) as paidClicksCommission,

(COALESCE(TempSales.Sales, 0)) as NumberOfSales,

ROUND(COALESCE(TempSales.unpaidSalesCom, 0), 3) as UnpaidSalesCommission,

ROUND(COALESCE(TempSales.paidSalesCom, 0), 3) as PaidSalesCommission,

ROUND(COALESCE(TempSales.PandingSalesCom, 0), 3) as PendingSalesCommission,

ROUND(COALESCE(TempSales.deniedSalesCom, 0), 3) as DeniedSalesCommission,

CONCAT(CAST(ROUND(AVG(COALESCE(TempSales.Sales, 0) / TempClicks.clicks), 2) as CHAR), ' %') as CTR,

ROUND(AVG(((COALESCE(TempSales.paidSalesCom, 0) + COALESCE(TempSales.unpaidSalesCom, 0)) / TempClicks.clicks)),3) as EPC

FROM

(SELECT

click.click_date as date,

click.fk_banner_id as bannerid,

click.fk_aff_id as affid,

click.fk_ts_id as tsid,

click.fk_sz_id as szid,

click.fk_partner_id as partnerid,

affiliate.aff_user as affname,

affiliate.aff_reffered_by as ref,

count(click.fk_aff_id) as clicks,

SUM(CASE click.click_com_stat WHEN 1 THEN click.click_full_com ELSE 0 END) as UnpaidClickCom,

SUM(CASE click.click_com_stat WHEN 5 THEN click.click_full_com ELSE 0 END) as paidClickCom

FROM click

INNER JOIN affiliate ON

click.fk_aff_id = affiliate.aff_id

WHERE

click.fk_aff_id = 8

AND

click.click_date BETWEEN '2006-06-01' AND '2007-08-12'

GROUP BY click.fk_ts_id, click.fk_banner_id) TempClicks

LEFT OUTER JOIN

(SELECT

sale.sale_date as date,

sale.fk_banner_id as bannerid,

sale.fk_aff_id as affid,

sale.fk_partner_id as partnerid,

sale.fk_ts_id as tsid,

sale.fk_sz_id as szid,

SUM(CASE sale.sale_com_stat WHEN 1 THEN 1 WHEN 5 THEN 1 ELSE 0 END) as sales,

SUM(CASE sale.sale_com_stat WHEN 1 THEN sale.sale_commision ELSE 0 END) as unpaidSalesCom,

SUM(CASE sale.sale_com_stat WHEN 5 THEN sale.sale_commision ELSE 0 END) as paidSalesCom,

SUM(CASE sale.sale_com_stat WHEN 4 THEN sale.sale_commision ELSE 0 END) as deniedSalesCom,

SUM(CASE sale.sale_com_stat WHEN 3 THEN sale.sale_commision ELSE 0 END) as PandingSalesCom

FROM sale

INNER JOIN affiliate ON

sale.fk_aff_id = affiliate.aff_id

WHERE

sale.fk_aff_id = 8

AND

sale_desc = 'Affiliate commision'

AND

sale.sale_date BETWEEN '2006-06-01' AND '2007-08-12'

GROUP BY sale.fk_ts_id, sale.fk_banner_id) TempSales

ON TempClicks.tsid = TempSales.tsid AND TempClicks.bannerid = TempSales.bannerid

LEFT OUTER JOIN

(SELECT

Impressions.imp_date as date,

Impressions.fk_banner_id as bannerid,

Impressions.fk_aff_id as affid,

banner.fk_partner_id as partnerid,

Impressions.fk_ts_id as tsid,

Impressions.fk_sz_id as szid,

SUM(Impressions.imp_sum) as impCount,

banner.banner_campaign as campid,

banner.banner_name as BannerName,

banner.banner_size as BannerSize,

banner_type.bt_heb as BannerType

FROM Impressions

LEFT JOIN banner ON

Impressions.fk_banner_id = banner.banner_id

INNER JOIN banner_type ON

banner.banner_type = banner_type.bt_ext

WHERE

(Impressions.fk_aff_id = 8)

AND

Impressions.imp_date BETWEEN '2006-06-01' AND '2007-08-12'

GROUP BY Impressions.fk_ts_id, Impressions.fk_banner_id) TempImp

ON

TempClicks.tsid = TempImp.tsid AND TempClicks.bannerid = TempImp.bannerid

LEFT OUTER JOIN

(SELECT

id as tsid,

name as tsname,

Traffic_src.aff_id as affid

FROM Traffic_src

INNER JOIN affiliate ON

Traffic_src.aff_id = affiliate.aff_id) Traffic

ON

TempClicks.tsid = Traffic.tsid AND TempClicks.affid = Traffic.affid

LEFT OUTER JOIN

(SELECT

sz.fk_aff_id as affid,

sz.sz_id as szid,

sz.sz_name as szname,

sz.sz_traffic_source as tsid

FROM sz

INNER JOIN affiliate ON

sz.fk_aff_id = affiliate.aff_id) Smartzone

ON

TempClicks.tsid = Smartzone.tsid AND TempClicks.affid = Smartzone.affid

LEFT OUTER JOIN

(SELECT

partner_id,

partner_name

FROM Partner

LEFT OUTER JOIN app_prod ON

Partner.partner_id = app_prod.fk_partner_id

) Partners

ON

Partners.partner_id = TempClicks.partnerid WHERE Partners.partner_id IS NOT NULL

AND /* Traffic source */ Traffic.tsname IS NOT NULL GROUP BY /* Traffic source */ Traffic.tsname , Traffic.tsid , /* Banner data */ TempImp.BannerName , TempImp.BannerSize , TempImp.BannerType ORDER BY /* Traffic source */ Traffic.tsname , Traffic.tsid , /* Banner data */ TempImp.BannerName , TempImp.BannerSize , TempImp.BannerType


And here is the EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 159 Using temporary; Using filesort
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 17
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 75
1 PRIMARY <derived5> ALL NULL NULL NULL NULL 918 Using where
1 PRIMARY <derived6> ALL NULL NULL NULL NULL 654
1 PRIMARY <derived7> ALL NULL NULL NULL NULL 5683 Using where
7 DERIVED Partner ALL NULL NULL NULL NULL 7
7 DERIVED app_prod ref App_prod_FKIndex2 App_prod_FKIndex2 2 wesell.Partner.partner_id 107 Using index
6 DERIVED sz ALL NULL NULL NULL NULL 717
6 DERIVED affiliate eq_ref PRIMARY PRIMARY 2 wesell.sz.fk_aff_id 1 Using where; Using index
5 DERIVED affiliate index PRIMARY PRIMARY 2 NULL 904 Using index
5 DERIVED Traffic_src ref AFF_RELATION AFF_RELATION 4 wesell.affiliate.aff_id 1 Using where
4 DERIVED Impressions ref aff_id,imp_date aff_id 2 355 Using where; Using temporary; Using filesort
4 DERIVED banner ref PRIMARY PRIMARY 2 wesell.Impressions.fk_banner_id 1
4 DERIVED banner_type range banner_relation banner_relation 3 NULL 5 Range checked for each record (index map: 0x2)
3 DERIVED affiliate const PRIMARY PRIMARY 2 1 Using index; Using temporary; Using filesort
3 DERIVED sale ref sale_date,fk_aff_id fk_aff_id 2 2482 Using where
2 DERIVED affiliate const PRIMARY PRIMARY 2 1 Using temporary; Using filesort
2 DERIVED click ref Click_FKIndex1,fk_aff_id Click_FKIndex1 2 848 Using where

i would say you need to re-examine every part of that query to see if it is really necessary

for example, let's have a look at the very last join (re-formatted for clarity) --what does this do? well, it appears to take each partner, and join it to all of its app_prods (whatever they may be)

however, notice that it's a LEFT OUTER JOIN

that means: return the partner, whether or not it has any app_prods

now look at the SELECT -- it's selecting only partner columns!!!

this means the query will return 1 or more occurrences of each partner

when these intermediate rows -- remember, it's 1 or more per partner -- are joined to the other stuff produced by the rest of the query, what is the result?

$deity only knows, although i bet that's the reason for the DISTINCT in the outer query