MySQL Forums
Forum List  »  Performance

Re: How to improve a view with left join?
Posted by: Rick James
Date: March 13, 2011 12:08AM

First, verify whether the query runs slow outside the VIEW.
You have
select  a.*, (a.in_ctn - ifnull(
                      ( SELECT  sum(b.out_ctn)
                            from  wms_stock_out b
                            where  b.key_ref=a.key_ref),0)) 'rmg_ctn',
        (a.in_sku-ifnull(
                      ( SELECT  sum(b.out_sku)
                            from  wms_stock_out b
                            where  b.key_ref=a.key_ref) ,0)) 'rmg_sku',
        (a.in_ttl_gw - ifnull(
                      ( SELECT  sum(b.out_ttl_gw)
                            from  wms_stock_out b
                            where  b.key_ref=a.key_ref),0)) 'rmg_gw',
        (a.in_ttl_nw - ifnull(
                      ( SELECT  sum(b.out_ttl_nw)
                            from  wms_stock_out b
                            where  b.key_ref=a.key_ref),0)) 'rmg_nw',
        (a.in_ttl_cbm - ifnull(
                      ( SELECT  sum(b.out_ttl_cbm)
                            from  wms_stock_out b
                            where  b.key_ref=a.key_ref),0)) 'rmg_cbm'
    from  wms_stock_in a
    left join  wms_stock_out b on a.key_ref=b.key_ref
    group by  a.key_ref, a.cust_item_no1,
        a.orig_wh_date;
The LEFT JOIN seems to be totally useless. Each of the subqueries already takes care of accessing wms_stock_out.

This might work better:
SELECT a.*,
       a.in_ctn - b.rmg_ctn,
       a.in_sku - b.rmg_sku,
       a.in_gw - b.rmg_gw ,
       a.in_nw - b.rmg_nw,
       a.in_cbm - b.rmg_cbm
    from  wms_stock_in a
    JOIN ( SELECT
                key_ref,
                IFNULL(sum(out_ctn), 0) AS rmg_ctn,
                IFNULL(sum(out_sku), 0) AS rmg_sku,
                IFNULL(sum(out_gw), 0) AS rmg_gw,
                IFNULL(sum(out_nw), 0) AS rmg_nw,
                IFNULL(sum(out_cbm), 0) AS rmg_cbm
            FROM wms_stock_out
            GROUP BY key_ref
         ) b  ON b.key_ref=a.key_ref
    group by  a.key_ref, a.cust_item_no1,
        a.orig_wh_date;
assuming a has an index on key_ref.

Or, maybe turn it inside out?
SELECT          a.*
                b.key_ref,
                IFNULL(sum(b.out_ctn), 0) AS rmg_ctn,
                IFNULL(sum(b.out_sku), 0) AS rmg_sku,
                IFNULL(sum(b.out_gw), 0) AS rmg_gw,
                IFNULL(sum(b.out_nw), 0) AS rmg_nw,
                IFNULL(sum(b.out_cbm), 0) AS rmg_cbm
    FROM wms_stock_out b
    JOIN wms_stock_in a  ON b.key_ref=a.key_ref
    group by  a.key_ref, a.cust_item_no1, a.orig_wh_date; 

Options: ReplyQuote


Subject
Views
Written By
Posted
2252
March 11, 2011 11:08AM
Re: How to improve a view with left join?
668
March 13, 2011 12:08AM


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.