Mysql query performance improvement for inventory calculation-CodePudding

I have a database structure like this:

Table: purchase_items:

 --------------------- --------------- ------ ----- --------- ---------------- 
| Field               | Type          | Null | Key | Default | Extra          |
 --------------------- --------------- ------ ----- --------- ---------------- 
| id                  | bigint(110)   | NO   | PRI | NULL    | auto_increment |
| purchase_id         | int(11)       | NO   | MUL | NULL    |                |
| product_id          | int(11)       | NO   | MUL | NULL    |                |
| units               | varchar(100)  | YES  |     | NULL    |                |
 --------------------- --------------- ------ ----- --------- ----------------

Table: sales_items:

 ----------------------- -------------- ------ ----- --------- ---------------- 
| Field                 | Type         | Null | Key | Default | Extra          |
 ----------------------- -------------- ------ ----- --------- ---------------- 
| id                    | int(11)      | NO   | PRI | NULL    | auto_increment |
| sales_id              | int(11)      | YES  | MUL | NULL    |                |
| product_id            | int(11)      | NO   | MUL | NULL    |                |
| purchase_item_id      | bigint(100)  | NO   | MUL | NULL    |                |
| qty                   | varchar(55)  | YES  |     | NULL    |                |
 ----------------------- -------------- ------ ----- --------- ----------------

There is a sales_return_items table similar to sales_items

I used to get the inventory as following:

SELECT
    pi.id,
    pi.product_id,
    pi.units,
    (
        SELECT SUM(si.qty) FROM sales_items si WHERE si.purchase_item_id = pi.id
    ) as sales_qty,
    (
        SELECT SUM(sri.qty) FROM sales_return_items sri WHERE sri.purchase_item_id = pi.id
    ) as sales_return_qty
FROM purchase_items pi

Above query used to get me the desired results.

But after adding a little more data error showing "request timeout" or taking more than 10 minutes to load the page.

My question is: How can I improve this query for performance?

CodePudding user response：

You could convert the correlated subqueries to joins:

SELECT
    pi.id,
    pi.product_id,
    pi.units,
    COALESCE(si.qty, 0) AS sales_qty,
    COALESCE(sri.qty, 0) AS sales_return_qty
FROM purchase_items pi
LEFT JOIN
(
    SELECT purchase_item_id, SUM(qty) AS qty 
    FROM sales_items
    GROUP BY purchase_item_id
) si
    ON si.purchase_item_id = pi.id
LEFT JOIN
(
    SELECT purchase_item_id, SUM(qty) AS qty
    FROM sales_return_items
    GROUP BY purchase_item_id
) sri
    ON sri.purchase_item_id = pi.id
GROUP BY
    pi.id;