I'm currently migrating from SQL Server to MySQL. When i try to match the number of rows i query information_Schema.tables. I noticed that there are some differences between the migrated information and the source information.

However, I did a select count(*) from table; gave me the correct number of rows

Why is there a difference between the information_Schema.tables.table_rows and the select count(*) from table?

  • Are you running these queries from the information_schema? Where is that table called table? Can you prefix it with the schema name so that you know where the data is coming from? – Vérace Jul 16 '15 at 19:19
  • Thank you Vérace they are in a schema called crmadmin. So when i run the query: select table_name,table_rows from information_schema.tables where table_schema='crmadmin' and table_name='table1'; and when i do select count(*) from crmadmin.table1; i get different results – kriegu Jul 16 '15 at 21:28
up vote 3 down vote accepted

The root cause is the storage engine. InnoDB does not update the information_schema.tables.table_rows column.

I have old posts that explain this further

You can get an approximation using SHOW INDEXES FROM against the table

If you would like to count all the tables in your current database regardless of the storage engine, just run this:

SET group_concat_max_len = 1024*1024*1024;
SELECT GROUP_CONCAT(sqltocount SEPARATOR ' UNION ') sqlcount INTO @sql
FROM (SELECT CONCAT('SELECT ''',table_name,''' tablename,COUNT(1) RowCount
FROM ',table_name) sqltocount FROM (SELECT table_name FROM
information_schema.tables WHERE table_schema=DATABASE()) AA) A;
SELECT @sql\G PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

GIVE IT A TRY !!!

Thanks to Rolando, his answer works fine. I also created a stored procedure to get that information, i'll post it if it's useful for somebody:

DROP PROCEDURE IF EXISTS mysql.getRowCount; DELIMITER // CREATE PROCEDURE mysql.getRowCount(DBNAME VARCHAR(30)) BEGIN DECLARE NO_DATA int default 0; DECLARE TBNAME varchar(30); DECLARE TBS CURSOR FOR select table_name from information_schema.tables where table_schema=DBNAME; DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_DATA=1; OPEN TBS; CREATE TEMPORARY TABLE IF NOT EXISTS table_count (table_schema varchar(30),table_name varchar(30),table_count varchar(11)) ENGINE=MEMORY; tbs:LOOP FETCH TBS into TBNAME; IF NO_DATA=1 THEN LEAVE tbs; END IF; SET @query=concat('SELECT count(*) into @CONT from ',DBNAME,'.',TBNAME,';'); PREPARE test FROM @query; EXECUTE test; DEALLOCATE PREPARE test; SET @query=concat('INSERT into table_count values ("',DBNAME,'","',TBNAME,'","',@CONT,'");'); PREPARE test FROM @query; EXECUTE test; DEALLOCATE PREPARE test; END LOOP tbs; CLOSE TBS; SELECT * from table_count; DROP TABLE table_count; END// DELIMITER ; call mysql.getRowCount('mysql');

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.