Is there a way to get the count of rows in all tables in a MySQL database without running a SELECT count() on each table?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
SELECT SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{your_db}';Note from the docs though: For InnoDB tables, the row count is only a rough estimate used in SQL optimization. You’ll need to use COUNT(*) for exact counts (which is more expensive).
Method 2
You can probably put something together with Tables table. I’ve never done it, but it looks like it has a column for TABLE_ROWS and one for TABLE NAME.
To get rows per table, you can use a query like this:
SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '**YOUR SCHEMA**';
Method 3
Like @Venkatramanan and others I found INFORMATION_SCHEMA.TABLES unreliable (using InnoDB, MySQL 5.1.44), giving different row counts each time I run it even on quiesced tables. Here’s a relatively hacky (but flexible/adaptable) way of generating a big SQL statement you can paste into a new query, without installing Ruby gems and stuff.
SELECT CONCAT(
'SELECT "',
table_name,
'" AS table_name, COUNT(*) AS exact_row_count FROM `',
table_schema,
'`.`',
table_name,
'` UNION '
)
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = '**my_schema**';It produces output like this:
SELECT "func" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.func UNION SELECT "general_log" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.general_log UNION SELECT "help_category" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_category UNION SELECT "help_keyword" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_keyword UNION SELECT "help_relation" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_relation UNION SELECT "help_topic" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_topic UNION SELECT "host" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.host UNION SELECT "ndb_binlog_index" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.ndb_binlog_index UNION
Copy and paste except for the last UNION to get nice output like,
+------------------+-----------------+ | table_name | exact_row_count | +------------------+-----------------+ | func | 0 | | general_log | 0 | | help_category | 37 | | help_keyword | 450 | | help_relation | 990 | | help_topic | 504 | | host | 0 | | ndb_binlog_index | 0 | +------------------+-----------------+ 8 rows in set (0.01 sec)
Method 4
I just run:
show table status;
This will give you the row count for EVERY table plus a bunch of other info.
I used to use the selected answer above, but this is much easier.
I’m not sure if this works with all versions, but I’m using 5.5 with InnoDB engine.
Method 5
Simple way:
SELECT
TABLE_NAME, SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '{Your_DB}'
GROUP BY TABLE_NAME;
Result example:
+----------------+-----------------+ | TABLE_NAME | SUM(TABLE_ROWS) | +----------------+-----------------+ | calls | 7533 | | courses | 179 | | course_modules | 298 | | departments | 58 | | faculties | 236 | | modules | 169 | | searches | 25423 | | sections | 532 | | universities | 57 | | users | 10293 | +----------------+-----------------+
Method 6
SELECT TABLE_NAME,SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_db' GROUP BY TABLE_NAME;
That’s all you need.
Method 7
This stored procedure lists tables, counts records, and produces a total number of records at the end.
To run it after adding this procedure:
CALL `COUNT_ALL_RECORDS_BY_TABLE` ();
–
The Procedure:
DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `COUNT_ALL_RECORDS_BY_TABLE`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE TNAME CHAR(255);
DECLARE table_names CURSOR for
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN table_names;
DROP TABLE IF EXISTS TCOUNTS;
CREATE TEMPORARY TABLE TCOUNTS
(
TABLE_NAME CHAR(255),
RECORD_COUNT INT
) ENGINE = MEMORY;
WHILE done = 0 DO
FETCH NEXT FROM table_names INTO TNAME;
IF done = 0 THEN
SET @SQL_TXT = CONCAT("INSERT INTO TCOUNTS(SELECT '" , TNAME , "' AS TABLE_NAME, COUNT(*) AS RECORD_COUNT FROM ", TNAME, ")");
PREPARE stmt_name FROM @SQL_TXT;
EXECUTE stmt_name;
DEALLOCATE PREPARE stmt_name;
END IF;
END WHILE;
CLOSE table_names;
SELECT * FROM TCOUNTS;
SELECT SUM(RECORD_COUNT) AS TOTAL_DATABASE_RECORD_CT FROM TCOUNTS;
ENDMethod 8
There’s a bit of a hack/workaround to this estimate problem.
Auto_Increment – for some reason this returns a much more accurate row count for your database if you have auto increment set up on tables.
Found this when exploring why show table info did not match up with the actual data.
SELECT table_schema 'Database', SUM(data_length + index_length) AS 'DBSize', SUM(TABLE_ROWS) AS DBRows, SUM(AUTO_INCREMENT) AS DBAutoIncCount FROM information_schema.tables GROUP BY table_schema; +--------------------+-----------+---------+----------------+ | Database | DBSize | DBRows | DBAutoIncCount | +--------------------+-----------+---------+----------------+ | Core | 35241984 | 76057 | 8341 | | information_schema | 163840 | NULL | NULL | | jspServ | 49152 | 11 | 856 | | mysql | 7069265 | 30023 | 1 | | net_snmp | 47415296 | 95123 | 324 | | performance_schema | 0 | 1395326 | NULL | | sys | 16384 | 6 | NULL | | WebCal | 655360 | 2809 | NULL | | WxObs | 494256128 | 530533 | 3066752 | +--------------------+-----------+---------+----------------+ 9 rows in set (0.40 sec)
You could then easily use PHP or whatever to return the max of the 2 data columns to give the “best estimate” for row count.
i.e.
SELECT table_schema 'Database', SUM(data_length + index_length) AS 'DBSize', GREATEST(SUM(TABLE_ROWS), SUM(AUTO_INCREMENT)) AS DBRows FROM information_schema.tables GROUP BY table_schema;
Auto Increment will always be +1 * (table count) rows off, but even with 4,000 tables and 3 million rows, that’s 99.9% accurate. Much better than the estimated rows.
The beauty of this is that the row counts returned in performance_schema are erased for you, as well, because greatest does not work on nulls. This may be an issue if you have no tables with auto increment, though.
Method 9
One more option: for non InnoDB it uses data from information_schema.TABLES (as it’s faster), for InnoDB – select count(*) to get the accurate count. Also it ignores views.
SET @table_schema = DATABASE();
-- or SET @table_schema = 'my_db_name';
SET GROUP_CONCAT_MAX_LEN=131072;
SET @selects = NULL;
SELECT GROUP_CONCAT(
'SELECT "', table_name,'" as TABLE_NAME, COUNT(*) as TABLE_ROWS FROM `', table_name, '`'
SEPARATOR 'nUNIONn') INTO @selects
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = @table_schema
AND ENGINE = 'InnoDB'
AND TABLE_TYPE = "BASE TABLE";
SELECT CONCAT_WS('nUNIONn',
CONCAT('SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND ENGINE <> "InnoDB" AND TABLE_TYPE = "BASE TABLE"'),
@selects) INTO @selects;
PREPARE stmt FROM @selects;
EXECUTE stmt USING @table_schema;
DEALLOCATE PREPARE stmt;If your database has a lot of big InnoDB tables counting all rows can take more time.
Method 10
You can try this. It is working fine for me.
SELECT IFNULL(table_schema,'Total') "Database",TableCount
FROM (SELECT COUNT(1) TableCount,table_schema
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql')
GROUP BY table_schema WITH ROLLUP) A;Method 11
If you use the database information_schema, you can use this mysql code (the where part makes the query not show tables that have a null value for rows):
SELECT TABLE_NAME, TABLE_ROWS FROM `TABLES` WHERE `TABLE_ROWS` >=0
Method 12
The following query produces a(nother) query that will get the value of count(*) for every table, from every schema, listed in information_schema.tables. The entire result of the query shown here – all rows taken together – comprise a valid SQL statement ending in a semicolon – no dangling ‘union’. The dangling union is avoided by use of a union in the query below.
select concat('select "', table_schema, '.', table_name, '" as `schema.table`,
count(*)
from ', table_schema, '.', table_name, ' union ') as 'Query Row'
from information_schema.tables
union
select '(select null, null limit 0);';Method 13
This is what I do to get the actual count (no using the schema)
It’s slower but more accurate.
It’s a two step process at
-
Get list of tables for your db. You can get it using
mysql -uroot -p mydb -e "show tables"
-
Create and assign the list of tables to the array variable in this bash script (separated by a single space just like in the code below)
array=( table1 table2 table3 ) for i in "${array[@]}" do echo $i mysql -uroot mydb -e "select count(*) from $i" done -
Run it:
chmod +x script.sh; ./script.sh
Method 14
This is how I count TABLES and ALL RECORDS using PHP:
$dtb = mysql_query("SHOW TABLES") or die (mysql_error());
$jmltbl = 0;
$jml_record = 0;
$jml_record = 0;
while ($row = mysql_fetch_array($dtb)) {
$sql1 = mysql_query("SELECT * FROM " . $row[0]);
$jml_record = mysql_num_rows($sql1);
echo "Table: " . $row[0] . ": " . $jml_record record . "<br>";
$jmltbl++;
$jml_record += $jml_record;
}
echo "--------------------------------<br>$jmltbl Tables, $jml_record > records.";Method 15
Poster wanted row counts without counting, but didn’t specify which table engine. With InnoDB, I only know one way, which is to count.
This is how I pick my potatoes:
# Put this function in your bash and call with:
# rowpicker DBUSER DBPASS DBNAME [TABLEPATTERN]
function rowpicker() {
UN=$1
PW=$2
DB=$3
if [ ! -z "$4" ]; then
PAT="LIKE '$4'"
tot=-2
else
PAT=""
tot=-1
fi
for t in `mysql -u "$UN" -p"$PW" "$DB" -e "SHOW TABLES $PAT"`;do
if [ $tot -lt 0 ]; then
echo "Skipping $t";
let "tot += 1";
else
c=`mysql -u "$UN" -p"$PW" "$DB" -e "SELECT count(*) FROM $t"`;
c=`echo $c | cut -d " " -f 2`;
echo "$t: $c";
let "tot += c";
fi;
done;
echo "total rows: $tot"
}I am making no assertions about this other than that this is a really ugly but effective way to get how many rows exist in each table in the database regardless of table engine and without having to have permission to install stored procedures, and without needing to install ruby or php. Yes, its rusty. Yes it counts. count(*) is accurate.
Method 16
Based on @Nathan’s answer above, but without needing to “remove the final union” and with the option to sort the output, I use the following SQL. It generates another SQL statement which then just run:
select CONCAT( 'select * from (n', group_concat( single_select SEPARATOR ' UNIONn'), 'n ) Q order by Q.exact_row_count desc') as sql_query
from (
SELECT CONCAT(
'SELECT "',
table_name,
'" AS table_name, COUNT(1) AS exact_row_count
FROM `',
table_schema,
'`.`',
table_name,
'`'
) as single_select
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'YOUR_SCHEMA_NAME'
and table_type = 'BASE TABLE'
) QYou do need a sufficiently large value of group_concat_max_len server variable but from MariaDb 10.2.4 it should default to 1M.
Method 17
If you want the exact numbers, use the following ruby script. You need Ruby and RubyGems.
Install following Gems:
$> gem install dbi $> gem install dbd-mysql
File: count_table_records.rb
require 'rubygems'
require 'dbi'
db_handler = DBI.connect('DBI:Mysql:database_name:localhost', 'username', 'password')
# Collect all Tables
sql_1 = db_handler.prepare('SHOW tables;')
sql_1.execute
tables = sql_1.map { |row| row[0]}
sql_1.finish
tables.each do |table_name|
sql_2 = db_handler.prepare("SELECT count(*) FROM #{table_name};")
sql_2.execute
sql_2.each do |row|
puts "Table #{table_name} has #{row[0]} rows."
end
sql_2.finish
end
db_handler.disconnectGo back to the command-line:
$> ruby count_table_records.rb
Output:
Table users has 7328974 rows.
Method 18
The code below generation the select query for all tales. Just delete last “UNION ALL” select all result and paste a new query window to run.
SELECT
concat('select ''', table_name ,''' as TableName, COUNT(*) as RowCount from ' , table_name , ' UNION ALL ') as TR FROM
information_schema.tables where
table_schema = 'Database Name'Method 19
If you know the number of tables and their names, and assuming they each have primary keys, you can use a cross join in combination with COUNT(distinct
) to get the rows that come from each table:
SELECT COUNT(distinct t1.id) + COUNT(distinct t2.id) + COUNT(distinct t3.id) AS totalRows FROM firstTable t1, secondTable t2, thirdTable t3;
Here is an SQL Fiddle example.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0