SQL & PL/SQL

Hi,

I've a view as follows with a very bad performance in MySQL. 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 or any other good suggestions?

Thanks

Benny