Count query from different databases with same schema

Question

Is there any simple way to get count data of single table from different databases(db names) with same schema.

I tried like this..

SELECT COUNT(id) AS db1users,
(SELECT COUNT(id) FROM DB2.users) AS db2users,(
SELECT COUNT(id) FROM DB3.users) AS db3users,
(SELECT COUNT(id) FROM DB4.users) AS db4users,
..........
..........
.......... 
FROM DB1.users;

I got the exact result but query becoming very large. Is there any simple way to get this..

Please help

You will need to add schema name as well, like FROM DB1.dbo.users, there cannot be more Simpler way. — Bambam Deo, Sep 09 '19 at 09:47
i would tend to avoid `SELECT COUNT(id) FROM DB4.users` on InnoDB as that needs to scan the complete table/index to `COUNT()` .. i you don't have a other option use MySQL 8.0.14+ Parallel Query support for [COUNT()](https://www.percona.com/blog/2019/01/23/mysql-8-0-14-a-road-to-parallel-query-execution-is-wide-open/) , yes fully Parallel Query support seams to be on it way in MySQL 8.. i would advice look if @GMB 's answer might work for you — Raymond Nijland, Sep 09 '19 at 10:16

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Another option, that avoids the need for dynamic sql (and is far less expensive, hence much more scalable), would be to use MySQL the INFORMATION_SCHEMA.TABLES table. It has a column named TABLE_ROWS, whose specification is as follows:

TABLE_ROWS

The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

If this matches your requirement, then you can use a simple query loke:

SELECT table_schema, table_rows FROM information_schema.tables WHERE table_name = 'users'

score 1 · Answer 2 · answered Sep 09 '19 at 09:52

The best way to do this would be via a scripting language (e.g. Python, Ruby, PHP); you'd execute a database query to get all the database names from your database, then create a SQL statement with all your select count(id) from...; once you've built the SQL statement, you'd execute it.

You can also do this in dynamic SQL inside MySQL; dynamic SQL is hard to write and debug, so I'm not a huge fan....

Count query from different databases with same schema

2 Answers2