SQL : Faster MySQL Counting on JOINS

faster-mysql-counting-on-joins.sql

SELECT
  user_accounts.id,
  user_accounts.company_name,
  IFNULL(suppliers.count, 0)              AS suppliers,
  IFNULL(customers.count, 0)              AS customers,
  IFNULL(inventory_transactions.count, 0) AS transactions
FROM user_accounts

  LEFT JOIN (SELECT
               user_account_id,
               COUNT(*) AS count
             FROM inventory_transactions
             GROUP BY user_account_id) inventory_transactions
    ON inventory_transactions.user_account_id = user_accounts.id

  LEFT JOIN (SELECT
               user_account_id,
               COUNT(*) AS count
             FROM customers
             GROUP BY user_account_id) customers
    ON customers.user_account_id = user_accounts.id

  LEFT JOIN (SELECT
               user_account_id,
               COUNT(*) AS count
             FROM suppliers
             GROUP BY user_account_id) suppliers
    ON suppliers.user_account_id = user_accounts.id

GROUP BY user_accounts.id