MySQL query calculation need long time

MySQL query calculation need long time

I have a problem with mysql.
I have a database warehousing. in the existing database tables goods,
incoming, outgoing. in table goods have data 2400 record and in table
incoming have data 80000 record. And then table outgoing have data 75625
records
I want to calculate the total incoming - outgoing goods based code, but it
takes quite a long time. I also had to change the calculation on the
application side but still require considerable time
is there a solution to this problem?
schema table goods
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment | | kode_barang |
varchar(20) | YES | | NULL | | | nama | varchar(100) | YES | | NULL | | |
satuan | varchar(20) | YES | | NULL | | | harga_beli | double | YES | |
NULL | | | harga_jual | double | YES | | NULL | | | stok | double | YES |
| NULL | | | kategori_barang | varchar(25) | YES | | NULL | | | stok_awal
| double | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
schema table incoming
+--------------+--------------+------+-----+---------+-------+ | Field |
Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+ | id |
int(8) | NO | | 0 | | | jenis_dokpab | varchar(100) | NO | | NULL | | |
no_dokpab | varchar(100) | NO | | NULL | | | tgl_dokpab | date | NO | |
NULL | | | no_invoice | varchar(100) | NO | | NULL | | | tgl_invoice |
date | NO | | NULL | | | pemasok | varchar(125) | NO | | NULL | | | kode |
varchar(100) | NO | | NULL | | | nama | varchar(100) | NO | | NULL | | |
jumlah | double | YES | | NULL | | | satuan | varchar(100) | NO | | NULL |
| | valuta | varchar(100) | NO | | NULL | | | nilai | varchar(100) | NO |
| NULL | | | kategori | varchar(100) | NO | | NULL | |
+--------------+--------------+------+-----+---------+-------+
table schema outgoing
+-------------+--------------+------+-----+---------+-------+ | Field |
Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+ | id |
int(11) | NO | | 0 | | | idsurat | int(11) | YES | | NULL | | |
kode_barang | varchar(20) | YES | | NULL | | | nama_barang | varchar(100)
| YES | | NULL | | | jumlah | double | YES | | NULL | | | satuan |
varchar(20) | YES | | NULL | | | kategori | varchar(30) | YES | | NULL | |
| no_surat | varchar(30) | YES | | NULL | | | tanggal | date | YES | |
NULL | | +-------------+--------------+------+-----+---------+-------+
my query
SELECT COALESCE(msk.total_msk,0) + COALESCE(masuk_produksi.total_msk,0) AS
incoming, COALESCE(keluar.total_klr,0) AS outgoing,br.kode_barang as kode,
br.nama,br.stok_awal,br.satuan,COALESCE(adj.total,0) AS
penyesuaian,COALESCE(msk.total_msk,0) + COALESCE(adj.total,0) +
COALESCE(br.stok_awal,0) + COALESCE(masuk_produksi.total_msk,0) -
COALESCE(keluar.total_klr,0) as stok_akhir,so.stok_opname from ( select
kode_barang,nama,stok_awal,satuan from barang where kategori_barang=1 ) as
br LEFT JOIN ( select (select sum(jumlah) from vw_sj_bahanbaku where
tanggal >= '$start' AND tanggal <='$end' and kode_barang=a.kode_barang) as
total_klr,a.kode_barang,a.nama_barang,a.satuan from vw_sj_bahanbaku a
group by a.kode_barang ) as keluar ON keluar.kode_barang=br.kode_barang
LEFT JOIN ( SELECT( SELECT SUM(jumlah) FROM adjusment WHERE status = '+'
AND tanggal >= '$start' AND tanggal <= '$end' ) - ( SELECT SUM(jumlah)
FROM adjusment WHERE status = '-' AND tanggal >= '$start' AND tanggal <=
'$end' ) AS total,kode_barang FROM adjusment GROUP BY kode_barang ) AS adj
ON br.kode_barang = adj.kode_barang LEFT JOIN ( select (select sum(jumlah)
from vw_incoming where tgl_dokpab >= '$start' AND tgl_dokpab <='$end' and
kode=a.kode) as total_msk,a.kode,a.nama from vw_incoming a group by a.kode
) as msk ON br.kode_barang=msk.kode LEFT JOIN ( select (select sum(jumlah)
from vw_outgoing where tanggal >= '$start' AND tanggal <='$end' and
kode_barang=a.kode_barang) as total_msk,a.kode_barang,a.nama_barang from
vw_produksi a where a.kategori='Bahan Baku' group by a.kode_barang ) as
masuk_produksi ON br.kode_barang=masuk_produksi.kode_barang LEFT JOIN (
select(select sum(jumlah) from pencacahan where tgl_stok_opname >=
'2013-04-01' AND tgl_stok_opname <= '2013-05-31' AND
kode_barang=a.kode_barang) as stok_opname,a.kode_barang from pencacahan a
group by a.kode_barang ) as so ON br.kode_barang=so.kode_barang