Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2007
    Posts
    1

    Unanswered: Very bad query profermance

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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) --
    Code:
    SELECT partner_id
         , partner_name
      FROM Partner 
    LEFT OUTER 
      JOIN app_prod 
        ON Partner.partner_id = app_prod.fk_partner_id
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •