how to make a simple subquery in mysql and high performance
Posted by: Yusron Nube
Date: October 25, 2013 03:29AM
Date: October 25, 2013 03:29AM
how do you get when when doing a query does not require a long time
I have a table schema like this.
In table material_out I have data of 10,000 rows. and has as many as 350 types of barcodes. In table material_in I have data around 15,000 rows. and has as many as 200 types of barcodes.
**my query is like this**
I have used a query like that. the result is going well. but when I try to enter new data. I then re-query of your display. but the results are not in accordance with the entered data. for example, when I enter a query from you. The following results
When I do a manual summation and results like this.
Why the results can be very much different, when the data is very vital for reporting. Please help me.
I used this earlier but it took a long time to execute. maybe you can help to summarize this query to quickly executed
Edited 1 time(s). Last edit at 10/27/2013 09:23PM by Yusron Nube.
I have a table schema like this.
mysql> desc material_out; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | barcode | varchar(20) | NO | | NULL | | | name | varchar(100) | NO | | NULL | | | qty | double | YES | | NULL | | | unit | varchar(20) | YES | | NULL | | | num_letter | varchar(30) | YES | | NULL | | | date | date | YES | | NULL | | | destination | varchar(50) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) mysql> desc material_in; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | barcode | varchar(20) | NO | | NULL | | | name | varchar(100) | NO | | NULL | | | qty | double | YES | | NULL | | | unit | varchar(20) | YES | | NULL | | | num_letter | varchar(30) | YES | | NULL | | | date | date | YES | | NULL | | | destination | varchar(50) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) mysql> desc goods; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | barcode | varchar(20) | NO | | NULL | | | name | varchar(100) | NO | | NULL | | | unit | varchar(20) | YES | | NULL | | | category | varchar(25) | YES | | NULL | | | first_stok | double ) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
In table material_out I have data of 10,000 rows. and has as many as 350 types of barcodes. In table material_in I have data around 15,000 rows. and has as many as 200 types of barcodes.
**my query is like this**
SELECT br.barcode, COALESCE(tNEW.total_out,0) AS total_out, COALESCE(tNEW.total_in,0) as total_in, COALESCE(tNEW.total_in,0)-COALESCE(tNEW.total_out,0) AS result, COALESCE(tOLD.total_out,0) AS total_out_old, COALESCE(tOLD.total_in,0) AS total_in_old FROM ( SELECT barcode from goods where category=1 ) as br LEFT JOIN ( SELECT goods.barcode, COALESCE(SUM(tOUT.qty),0) AS total_out, COALESCE(SUM(tIN.qty),0) AS total_in FROM goods LEFT JOIN material_out AS tOUT ON tOUT.barcode=goods.barcode LEFT JOIN material_in AS tIN ON tIN.barcode=goods.barcode WHERE goods.category=1 AND tOUT.date >='2013-05-01' AND tOUT.date <='2013-08-31' AND tIN.date >= '2013-05-01' AND tIN.date <= '2013-08-31' GROUP BY goods.barcode ) AS tNEW ON tNEW.barcode=br.barcode LEFT JOIN ( SELECT goods.barcode, SUM(tOUT.qty) AS total_out, SUM(tIN.qty) AS total_in FROM goods LEFT JOIN material_out AS tOUT ON tOUT.barcode=goods.barcode LEFT JOIN material_in AS tIN ON tIN.kode=goods.barcode WHERE goods.category=1 AND tOUT.date BETWEEN '2013-01-01' AND '2013-04-31' AND tIN.date BETWEEN '2013-01-01' AND '2013-04-31' GROUP BY goods.barcode ) AS tOLD ON tOLD.barcode=br.barcode
I have used a query like that. the result is going well. but when I try to enter new data. I then re-query of your display. but the results are not in accordance with the entered data. for example, when I enter a query from you. The following results
+--------------+-----------+----------+------------+---------------+-------------------+ | barcode | total_out | total_in | result | total_out_old | total_in_old | +--------------+-----------+----------+------------+---------------+-------------------+ | TNWET021 | 6195 | 15000 | 8805 | 20085 | 46200 | | TNWET020 | 3420 | 7650 | 4230 | 4860 | 23925 | | TNWET019 | 8370 | 25200 | 16830 | 11610 | 47175 | | TNWET018 | 18690 | 44100 | 25410 | 13800 | 54150 | | TNWET017 | 1140 | 3750 | 2610 | 3690 | 16200 | | TNWET016 | 19500 | 56100 | 36600 | 31725 | 111300 | | TNWET015 | 5145 | 18150 | 13005 | 6510 | 23400 | | TNWET014 | 33300 | 65250 | 31950 | 96300 | 262500 | | TNWET013 | 1170 | 5625 | 4455 | 3690 | 13200 | | TNWET012 | 720 | 2700 | 1980 | 3870 | 13800 | | TNWET011 | 0 | 0 | 0 | 180 | 450 | | TNWET010 | 0 | 0 | 0 | 405 | 1125 | | TNWET009 | 0 | 0 | 0 | 0 | 0 |
When I do a manual summation and results like this.
mysql> select sum(qty) from material_in where barcode='TNWET021' and date BETWEEN '2013-05-01' AND '2013-08-31'; +-------------+ | sum(qty) | +-------------+ | 750 | +-------------+ 1 row in set (0.00 sec) mysql> select sum(qty) from material_in where barcode='TNWET020' and date BETWEEN '2013-05-01' AND '2013-08-31'; +-------------+ | sum(qty) | +-------------+ | 450 | +-------------+ 1 row in set (0.00 sec)
Why the results can be very much different, when the data is very vital for reporting. Please help me.
I used this earlier but it took a long time to execute. maybe you can help to summarize this query to quickly executed
SELECT COALESCE(tIN.total_in,0) + COALESCE(production.total_prod,0) AS incoming, COALESCE(tOUT.total_out,0) AS expenditure,br.barcode as barcode, br.name,br.initial_stock,br.unit,COALESCE(adj.total,0) AS adjusment,COALESCE(tIN.total_in,0) + COALESCE(production.total_prod,0) + COALESCE(adj.total,0) + COALESCE(br.intial_stock,0) - COALESCE(tOUT.total_out,0) as final_stok,so.stock_opname from ( select barcode,name,initial_stock,unit from barang where category=1 ) as br LEFT JOIN ( select (select sum(qty) from material_out where date >= '2013-05-01' AND date <='2013-08-31' and barcode=a.barcode) as total_out,a.barcode from material_out a group by a.barcode ) as tOUT ON tOUT.barcode=br.barcode LEFT JOIN ( SELECT( SELECT SUM(qty) FROM adjusment WHERE status = '+' AND date >= '2013-05-01' AND date <= '2013-08-31' ) - ( SELECT SUM(qty) FROM adjusment WHERE status = '-' AND date >= '2013-05-01' AND date <= '2013-08-31' ) AS total,barcode FROM adjusment GROUP BY barcode ) AS adj ON br.barcode = adj.barcode LEFT JOIN ( select (select sum(qty) from material_in where date >= '2013-05-01' AND date <='2013-08-31' and barcode=a.barcode) as total_in,a.barcode,a.nama from material_in a group by a.barcode ) as tIN ON br.barcode=tIN.barcode LEFT JOIN ( select (select sum(qty) from view_production where date >= '$start' AND date <='$end' and kode=a.kode) as total_prod,a.barcode from view_production a group by a.barcode ) as production ON br.barcode=production.barcode LEFT JOIN ( select(select sum(qty) from stock_opname where date >= '2013-04-01' AND date <= '2013-05-31' AND barcode=a.barcode) as stok_opname,a.barcode from pencacahan a group by a.barcode ) as so ON br.barcode=so.barcode
Edited 1 time(s). Last edit at 10/27/2013 09:23PM by Yusron Nube.
Subject
Views
Written By
Posted
how to make a simple subquery in mysql and high performance
2856
October 25, 2013 03:29AM
1072
October 26, 2013 09:42AM
898
October 27, 2013 09:31PM
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.