Optimize Query Mysql to count data in each district

i have this query for calculate success total in each district. this query works but its take until 2min to output data, i have 15k rows in orders.

SELECT 
    nsf.id, 
    nsf.province,
    nsf.city,
    nsf.district,
    nsf.shipping_fee,
    IFNULL((SELECT COUNT(orders.id) FROM orders
        JOIN users ON orders.customer_id = users.id
        JOIN addresses ON addresses.user_id = users.id
        JOIN subdistricts ON subdistricts.id = addresses.subdistrict_id
        WHERE orders.status_tracking IN ("Completed","Successful Delivery")
        AND subdistricts.ninja_fee_id = nsf.id
        AND orders.transfer_to = "cod"),0) as success_total
from ninja_shipping_fees nsf
GROUP BY nsf.id
ORDER BY nsf.province;

the output should be like this

can you help me to improve the peformance? Thanks

Answer

Try performing the grouping/calculation in a joined “derived table” instead of a “correlated subquery”

SELECT
    nsf.id
  , nsf.province
  , nsf.city
  , nsf.district
  , nsf.shipping_fee
  , COALESCE( g.order_count, 0 ) AS success_total
FROM ninja_shipping_fees nsf
LEFT JOIN (
    SELECT
        subdistricts.ninja_fee_id
      , COUNT( orders.id ) AS order_count
    FROM orders
    JOIN users ON orders.customer_id = users.id
    JOIN addresses ON addresses.user_id = users.id
    JOIN subdistricts ON subdistricts.id = addresses.subdistrict_id
    WHERE orders.status_tracking IN ('Completed', 'Successful Delivery')
    AND orders.transfer_to = 'cod'
    GROUP BY subdistricts.ninja_fee_id
    ) AS g ON g.ninja_fee_id = nsf.id
ORDER BY nsf.province;

“Correlated subqueries” are often a source of poor performance.

Other notes, I prefer to use COALESCE() because it is ANSI standard and available in most SQL implementations now. Single quotes are more typically used to denote strings literals.