We have a multi-tenant application where:
- Each tenant gets its own database (with for instance a table
user) - A landlord table stores all tenants and its corresponding DB name
We need to get statistics across all tenants and before doing it the slow way with a PHP loop, I wanted to try doing it directly on the MySQL server.
From my research, and given my limited knowledge on prepared statements, I could come up with that code:
<code># landlord.tenants table has a db_name column
# each tenant DB has a users table
# We want to know how many users in total we have on the app
SELECT
GROUP_CONCAT(
CONCAT(
'(SELECT count(id) FROM `',
db_name,
'`.`users`)'
)
SEPARATOR ' + '
) as tmp
FROM
landlord.tenants
INTO @sql;
SET @sql := CONCAT('SELECT ', @sql);
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
</code>
<code># landlord.tenants table has a db_name column
# each tenant DB has a users table
# We want to know how many users in total we have on the app
SELECT
GROUP_CONCAT(
CONCAT(
'(SELECT count(id) FROM `',
db_name,
'`.`users`)'
)
SEPARATOR ' + '
) as tmp
FROM
landlord.tenants
INTO @sql;
SET @sql := CONCAT('SELECT ', @sql);
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
</code>
# landlord.tenants table has a db_name column
# each tenant DB has a users table
# We want to know how many users in total we have on the app
SELECT
GROUP_CONCAT(
CONCAT(
'(SELECT count(id) FROM `',
db_name,
'`.`users`)'
)
SEPARATOR ' + '
) as tmp
FROM
landlord.tenants
INTO @sql;
SET @sql := CONCAT('SELECT ', @sql);
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
This works when you do not have many tenants. However, the result in our case is not correct because apparently GROUP_CONCAT has a limited maximum length which gets exceeded here.
How would you generalize such a request?
PS: most useful link has been SQL query to sum up count across multiple databases on same host from which I forged my own query.