如何管理MySQL中的慢速查询与2表Master Detail逐项比较

Question

请帮帮我。。我有一个如下的问题：这是一张表格：

1. delivery_order
2. 交货_订单_项目
3. bst
4. bst_item

我需要的是，我想根据KODE_BARANG和JUMLAH，显示delivery_order/项目表中不在bst/项目表中的商品

我有下面的查询，但如果数据已经很多，它仍然很慢(当比较KODE_BARANG时，慢的部分在行中(，我想问一下，我的朋友中是否有人经历过类似的案件，以及如何解决？提前谢谢你，我希望这里有人愿意回答。

SELECT
del.KODE_DO,
deli.KODE_BARANG,
deli.NAMA_BARANG,
deli.JUMLAH,
deli.SATUAN,
@DITERIMA := COALESCE ((
SELECT
SUM( JUMLAH ) 
FROM
bst_item 
WHERE
KODE_PENERIMAAN = deli.KODE_DO
AND KODE_BARANG = deli.KODE_BARANG  #this line that makes it slow 
AND `STATUS` <> 0),0) AS DITERIMA,
COALESCE ( deli.JUMLAH, 0 ) - @DITERIMA AS SISA
FROM
delivery_order del
INNER JOIN delivery_order_item deli ON del.KODE_DO = deli.KODE_DO
WHERE
DATE(del.TANGGAL) >= :TGL1
AND DATE(del.TANGGAL) <= :TGL2  
AND COALESCE ( deli.JUMLAH, 0 ) - COALESCE ((
SELECT
SUM( JUMLAH ) 
FROM
bst_item 
WHERE
KODE_PENERIMAAN = deli.KODE_DO
AND KODE_BARANG = deli.KODE_BARANG  #this line that makes it slow 
AND `STATUS` <> 0),0) > 0

SELECT
del.KODE_DO,
deli.KODE_BARANG,
deli.NAMA_BARANG,
deli.JUMLAH,
deli.SATUAN,
@DITERIMA := COALESCE ((
SELECT
SUM( JUMLAH ) 
FROM
bst_item 
WHERE
KODE_PENERIMAAN = deli.KODE_DO
AND KODE_BARANG = deli.KODE_BARANG  #this line that makes it slow 
AND `STATUS` <> 0),0) AS DITERIMA,
COALESCE ( deli.JUMLAH, 0 ) - @DITERIMA AS SISA
FROM
delivery_order del
INNER JOIN delivery_order_item deli ON del.KODE_DO = deli.KODE_DO
WHERE
DATE(del.TANGGAL) >= :TGL1
AND DATE(del.TANGGAL) <= :TGL2  
AND COALESCE ( deli.JUMLAH, 0 ) - COALESCE ((
SELECT
SUM( JUMLAH ) 
FROM
bst_item 
WHERE
KODE_PENERIMAAN = deli.KODE_DO
AND KODE_BARANG = deli.KODE_BARANG  #this line that makes it slow
AND `STATUS` <> 0),0) > 0

Answer 1

INDEX(KODE_PENERIMAAN, KODE_BARANG, `STATUS`)

并且不要在函数调用中隐藏TANGGAL。(cf"sargable"(