MySQL Information Schema – Not Showing Reliable Information?

information-schemainnodbMySQL

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?

Best Answer

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

Apr 23, 2012 : Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?
May 16, 2012 : Why doesn't InnoDB store the row count?
Mar 03, 2014 : The most efficient method to obtain the total entries into a mysql table

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 !!!

Related Solutions

Mysql – Strange behaviour with MySQL 5.1 TO_DAYS partitioning schema

sorry, the comment of @phil aroused my curiosity so I decided to cheat and to read the manual:

18.2.7. How MySQL Partitioning Handles NULL

Partitioning in MySQL does nothing to disallow NULL as the value of a partitioning expression, whether it is a column value or the value of a user-supplied expression. Even though it is permitted to use NULL as the value of an expression that must otherwise yield an integer, it is important to keep in mind that NULL is not a number. MySQL's partitioning implementation treats NULL as being less than any non-NULL value, just as ORDER BY does.

Mysql – use a foreign key index as a shortcut to getting a row count in an INNODB table

You cannot trust the EXPLAIN plan's row count. Why?

Each time Query Optimization takes place where it involves an InnoDB table, the InnoDB Storage Engine does an approximation of the BTREE entries for the keys. They approximations change from query to query.

Simply run SHOW INDEXES FROM guage_data; multiple times and you will get a different set of row counts each time. You could set innodb_stats_on_metadata to 0:

SET GLOBAL innodb_stats_on_metadata = 0;

This will cause SHOW INDEXES FROM guage_data; to give the same number over and over again. This is still self-defeating because the row count would still be approximated anyway. You would have to disable the metadata stats and run ANALYZE TABLE. Again, this is self-defeating.

With innodb_stats_on_metadata on, approximations are always done. I wrote about this back on June 21, 2011 : From where does the MySQL Query Optimizer read index statistics?

In light of all this, you are better off doing SELECT id FROM guage_data if id is the PRIMARY KEY. However, notice in your EXPLAIN plan that MySQL choose to traverse the gauge_data_FI_1 index. This makes all the sense in the world if this table is InnoDB. Why? Every non-unique index stores the rowid of the PRIMARY KEY anyway.

Best Answer

Related Solutions

Mysql – Strange behaviour with MySQL 5.1 TO_DAYS partitioning schema

Mysql – use a foreign key index as a shortcut to getting a row count in an INNODB table

Related Question