Skip navigation links

MySQL Forums :: Performance :: How to improve a view with left join?


Advanced Search

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? 1580 Benny Lam 03/11/2011 11:08AM
Re: How to improve a view with left join? 396 Rick James 03/13/2011 12:08AM
Re: How to improve a view with left join? 426 Benny Lam 03/14/2011 10:10PM


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.