How to manage MySQL slow queries when comparing two tables in a master-detail relationship: A step-by-step guide

Question

please help me.. I have a query like the following: This is a table:

1. delivery_order
2. delivery_order_item
3. bst
4. bst_item

I want to display items that exist in the delivery_order/item table but do not exist in the bst/item table, comparing based on KODE_BARANG and JUMLAH.

I have the following query statement, but it is still very slow if there is already a lot of data (the slow part is comparing the rows of KODE_BARANG). I would like to ask if any of my friends have a similar situation and how to solve it? Thanks in advance, hope someone here is willing to answer.

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  #这行使它变慢
            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  #这行使它变慢
            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  #这行使它变慢
            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  #这行使它变慢
            AND `STATUS` <> 0),0) > 0

Answer 1

INDEX(KODE_PENERIMAAN, KODE_BARANG, `STATUS`)

And don't hide TANGGAL in function calls. (cf "sargable")