Faster MySQL Counting on JOINS

Lets say you want to count how many customers, sales and suppliers/vendors some user in your system has, in a single query. A naive approach would be to do this:

SELECT
  user_accounts.id,
  user_accounts.company_name,
  COUNT(DISTINCT suppliers.id)              AS suppliers,
  COUNT(DISTINCT customers.id)     AS customers,
  COUNT(DISTINCT inventory_transactions.id) AS transactions
FROM user_accounts

  LEFT JOIN inventory_transactions
    ON inventory_transactions.user_account_id = user_accounts.id

  LEFT JOIN customers
    ON customers.user_account_id = user_accounts.id

  LEFT JOIN suppliers
    ON suppliers.user_account_id = user_accounts.id

GROUP BY user_accounts.id

Which could output something like:

idcompany_namesupplierscustomerstransactions
1Mohr PLC51641122
2Satterfield-Ankunding90231
3Cassin Ltd221416783
4Apple Inc.51990
5Google2231261
6Yahoo150

The problem is, that this query is crazy slow. For me, on my local machine, this query almost took 32 seconds, for under 1000 rows of data (the above table is randomized)

Sub-Queries

Instead we want to use sub-queries that count the data and returns it to the main query. Like so:

SELECT
  user_accounts.id,
  user_accounts.company_name,
  suppliers.count              AS suppliers,
  customers.count              AS customers,
  inventory_transactions.count 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

It may appear scary and excessive, but it's really not that much more to write. What you do is write a SELECT in your JOIN that counts the data, and in your main select you simply use the result.

Anyway, it's very much worth it. This query was only 17ms, which is.. well.. a 99.94% decrease in time.

A note on this, is that if there is no rows, the value will be null, instead of zero. You can however combat this using IFNULL:

IFNULL(suppliers.count, 0)
Show Comments