MySQL Forums
Forum List  »  Performance

How to improve a view with left join?
Posted by: Benny Lam
Date: March 11, 2011 11:08AM

Hi,

I've a view as follows with a very bad performance. This view is to select all fields and calculated values (rmg_ctn, rmg_sku, rmg_gw, rmg_nw and rmg_cbm) from a Stock In table left join with a Stock Out table with a index key (key_ref):

CREATE VIEW `wms_stock_rmg` AS
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 EXPLAIN of this view gives the following information

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a ALL null null null 14175 Using temporary; Using filesort
1 PRIMARY b ref key_ref key_ref 47 a.key_ref 2 Using index
6 DEPENDENT SUBQUERY b ref key_ef key_ref 47 func 2
5 DEPENDENT SUBQUERY b ref key_ref key_ref 47 func 2
4 DEPENDENT SUBQUERY b ref key_ref key_ref 47 func 2
3 DEPENDENT SUBQUERY b ref key_ref key_ref 47 func 2
2 DEPENDENT SUBQUERY b ref key_ref key_ref 47 func 2

Since both table a (Stock In) and table b (Stock Out) have added with an index (key_ref), I don't understand why the EXPLAIN shows there is null key for table a.

How can I improve the performance of this view? Should I put the sum() function in a Store Procedure in MySQL or any other good suggestions?

Thanks

Benny

Options: ReplyQuote


Subject
Views
Written By
Posted
How to improve a view with left join?
1909
March 11, 2011 11: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.