3
\$\begingroup\$

In the following query, I am fetching all the projects items with specific project id. At the same time, I am using a join in Select to fetch the delivered quantity of items of this project only. The delivered quantity join feels very costly because if I have 7000 items, it will multiply the iterations. In there any better solution. It can be SQL related, backend programming or anything else.

SELECT
    msprojectitems.idJobItem,
    msprojectitems.project_id,
    msprojectitems.hashcode,
    msprojectitems.itemID,
    msprojectitems.itemName,
    msprojectitems.itemNum,
    msprojectitems.itemWeight AS totalWeight,
    msprojectitems.itemUnit,
    msprojectitems.deleted,
    msprojectitems.LastUpdated,
    msprojectitems.itemWeight_assigned AS assignedWeight,
    IFNULL(
        (
            SELECT
                Sum(
                    IFNULL(
                        msjobitems.itemWeight_loaded,
                        msjobitems.itemWeight
                    )
                ) AS quantity_delivered
            FROM
                msjobcustomer
            INNER JOIN msjobcusttoitems ON msjobcustomer.idJobCustomer = msjobcusttoitems.idJobCustomer
            INNER JOIN msjobitems ON msjobcusttoitems.idJobItem = msjobitems.idJobItem
            WHERE
                msjobcustomer.project_id = 10
            AND msjobitems.itemID = msprojectitems.itemID
            AND msjobcustomer.jobstatus = 1
            GROUP BY
                msjobitems.itemID
        ),
        0
    ) AS quantity_delivered
FROM
    msprojectitems
WHERE
    msprojectitems.project_id = 10
ORDER BY
    msprojectitems.itemID ASC

Result of Expanded Explain of this query: enter image description here

\$\endgroup\$
3
\$\begingroup\$

It seems likely that the subquery in the SELECT is causing a row-by-row lookup. Try moving into a LEFT JOIN to get the set-based performance we're looking for:

SELECT
    msprojectitems.idJobItem,
    msprojectitems.project_id,
    msprojectitems.hashcode,
    msprojectitems.itemID,
    msprojectitems.itemName,
    msprojectitems.itemNum,
    msprojectitems.itemWeight AS totalWeight,
    msprojectitems.itemUnit,
    msprojectitems.deleted,
    msprojectitems.LastUpdated,
    msprojectitems.itemWeight_assigned AS assignedWeight,
    IFNULL(cte_quantity_delivered.quantity_delivered,0) AS quantity_delivered
FROM
    msprojectitems
LEFT JOIN
    (
        SELECT
            msjobitems.itemID,
            Sum(
                IFNULL(
                    msjobitems.itemWeight_loaded,
                    msjobitems.itemWeight
                )
            ) AS quantity_delivered
        FROM
            msjobcustomer
        INNER JOIN msjobcusttoitems ON msjobcustomer.idJobCustomer = msjobcusttoitems.idJobCustomer
        INNER JOIN msjobitems ON msjobcusttoitems.idJobItem = msjobitems.idJobItem
        WHERE
            msjobcustomer.project_id = 10
        AND msjobcustomer.jobstatus = 1
        GROUP BY
            msjobitems.itemID
    ) cte_quantity_delivered ON cte_quantity_delivered.itemID = msprojectitems.itemID
WHERE
    msprojectitems.project_id = 10
ORDER BY
    msprojectitems.itemID ASC
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.