I have a MySQL database with many tables. I follow a tenant based separation, where the every table contains a tenant_id column. Maximum rows per table would be ~billions and average would be in ~300 million. I need to periodically determine the approximate row count for each tenant.

Eg Tables would be:

• id,tenant_id,email,name
• id,tenant_id,product,price

tenant_id is an index in every table.

I am using MySQL8, and the information need not be very accurate/real-time. I am looking at something like a once-a-day job.

Things I’ve tried

• SHOW TABLE STATUS shows me the total row count, but not the tenant level metrics.
• Good-old count/group by query. Doesn’t even run and get me data for the smaller tables.