尝试在联接的“派生表”而不是“相关子查询”中执行分组/计算

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;

“相关子查询”通常是性能不佳的一个原因。
其他注意事项，我更喜欢使用coalesce（），因为它是ansi标准，现在在大多数sql实现中都可用。单引号通常用于表示字符串和文本。