Exact count of all rows in MySQL database

Question

I'm currently using the script

SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'Tables';

However, it's not accurate, because the engine used by my MySQL tables is InnoDB (I only realised this could be an issue now, be these databases have existed for a while).

Is there any way to get an exact count of every row in every table of a database with MySQL?

Cheers.

Because I accidently dropped an 's' in my example. I actually need to count the rows from all the tables in a particular database. — Connor Deckers, Jun 06 '12 at 10:52
Extended answer: http://stackoverflow.com/questions/24707814/mysql-summarize-all-table-row-counts-in-a-single-query — gwideman, Jul 11 '14 at 23:53
This is NOT a duplicate of http://stackoverflow.com/questions/2692340/mysql-count-all-rows-per-table-for-in-one-query. This question specifically asks about databases with InnoDB tables. — g-dog, Jun 26 '15 at 15:34

score 14 · Accepted Answer · answered Jun 06 '12 at 10:52

14

I think the only accurate (and slower) way is to do for every single table:

SELECT COUNT(*) FROM Table

answered Jun 06 '12 at 10:52

jkrcma

1,180
9
14

@vearutop Dragon Jake mentions in his/her answer that this would need to be run on every single table... – Ben Swinburne Jun 06 '12 at 11:02
2

I wouldn't put everything in SQL query, it seems that MySQL isn't capable of selecting from variable table name. So i came up at least with query generator: `SELECT CONCAT('SELECT COUNT(*) FROM ', table_name) FROM information_schema.tables WHERE table_schema = 'Tables';` – jkrcma Jun 06 '12 at 11:06
Cheers @DragonJake, turns out that's what I had to do. :) – Connor Deckers Jun 06 '12 at 22:19

score 5 · Answer 2 · answered Oct 09 '12 at 01:37

This SQL statement will generate a union query that can complete the task in one step:

SELECT CONCAT('SELECT \'', table_name ,'\' as tbl, (SELECT COUNT(*) FROM ', table_name, ') AS ct UNION')
FROM information_schema.tables
WHERE table_schema = 'your_database';

After running the SQL statement, just paste the output into a text editor, delete the word "UNION" from the end of the output, and you will have the union query.

Beware that running the union query could take a lot of resources depending on your database size, load, and hardware, so you may not want to run it all at once.

score 3 · Answer 3 · answered Feb 19 '13 at 14:40

I was having the same problem which led me to this question.

Thanks to computerGuy12345's answer, I came up with a stored procedure that does this without having to copy-paste. As he said, beware that this will scan each table and might take a while.

DELIMITER $$

CREATE PROCEDURE `all_tables_rowcount`(databaseName VARCHAR(250))
BEGIN
DECLARE p_done INT DEFAULT FALSE;
DECLARE p_queryString varchar(250) ;
DECLARE p_cur CURSOR FOR SELECT queryString FROM tmp.tableCountQueries;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET p_done = TRUE;

DROP TEMPORARY TABLE IF EXISTS tmp.tableCountQueries;
DROP TEMPORARY TABLE IF EXISTS tmp.tableCounts;

CREATE TEMPORARY TABLE tmp.tableCounts(TableName varchar(250), RowCount BIGINT) ;

CREATE TEMPORARY TABLE tmp.tableCountQueries(queryString varchar(250)) AS 
(
    SELECT CONCAT(
        'INSERT INTO tmp.tableCounts(TableName, RowCount) SELECT "', 
        table_name, 
        '", COUNT(*) AS RowCount FROM ', 
        table_schema,
        '.',
        table_name
    ) AS queryString
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE table_schema = databaseName
) ;

OPEN p_cur;

read_loop: LOOP
    FETCH p_cur INTO p_queryString;
    IF p_done THEN
      LEAVE read_loop;
    END IF;
    SET @queryString = p_queryString ;
    PREPARE rowcountTable FROM @queryString; 
    EXECUTE rowcountTable ;
END LOOP;

SELECT * FROM tmp.tableCounts ;
END

score 1 · Answer 4 · edited Jun 06 '12 at 13:34

Select Sum(column_Name) from table ,can not give the exact count of rows in a table , it wil give total row count+1 , wil give the next data inserting row also. and one more thing is, in sum(Column_Name) the column_Name should be int ,if it is varchar or char sum function wont work. soo the best thing is use Select Count(*) from table to get exact number of rows in a table.

ThnQ,

Venkat

score 1 · Answer 5 · edited Dec 13 '12 at 18:07

1

select table_name,table_rows from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='database name';

table_name means number of tables which is present in "data base "

table_rows means number rows in each column

table_schema is the database name. from which data base you are looking for the counts

edited Dec 13 '12 at 18:07

James A Mohler

11,060
15
46
72

answered Dec 13 '12 at 17:45

saiprathapreddy

43
1

2

the table_rows count in INFORMATION_SCHEMA is not accurate for Innodb tables, it's only an approximation. – forforf Dec 25 '12 at 16:37
1

Agreed. This will work for non-InnoDB tables but does not address the specific question. – g-dog Jun 26 '15 at 15:28

score -2 · Answer 6 · answered Jun 06 '12 at 11:46

this worked for me

    <?php 

             mysql_connect("localhost", "root", "pass") or die(mysql_error());

             mysql_select_db("csl") or die(mysql_error());

             $dtb=mysql_query("SHOW TABLES") or die (mysql_error()); 

             $jmltbl=0; $jml_record=0; $jml_record=0; $total =0;


             while($row=mysql_fetch_array($dtb)) 

    { 

 $sql=mysql_query("select * from $row[0]");

 $jml_record=mysql_num_rows($sql);

 $total += $jml_record;   // this counts all the rows





 $jmltbl++; $jml_record+=$jml_record;




    } 
      // echo"--------$jmltbl Tables, $jml_record records."; // will print count of indivual table

echo $total;  // this will print you count of all rows in MySQL database 

       ?>

i have tested this code and its works

output 1145 for me

it was not slow for me it counted 1145 instantly all other schema related scanrios were not working for me they just gave null results — Rinzler, Jun 06 '12 at 11:47
-1 for returning all rows of all tables in the entire database. Use `count(*)` instead of `*`. — dotancohen, Dec 04 '13 at 07:27

Exact count of all rows in MySQL database

6 Answers6