Fetching all the project's items for a specific project id

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:

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

StackExchange Code Review Q#114835, answer score: 3