CODEWITHSUNDEEP
mysqldatabasedatabase-migrationinformation-schema
IDaedalusI
IDaedalusI

Reputation: 81

Why does my MySQL database's INFORMATION_SCHEMA not accurately represent the tables

I was migrating a database from a server to the AWS cloud, and decided to double check the success of the migration by comparing the number of entries in the tables of the old database and the new one.

I first noticed that of the 46 tables I migrated, 13 were different sizes, on further inspection I noticed that 9 of the 13 tables were actually bigger in the newer database than the old one. There are no scripts/code currently setup with either database that would change the data, let alone the amount of data.

I then further inspected one of the smaller tables (only 43 rows) in the old database and noticed that when running the below sql query, I was getting a return of 40 TABLE_ROWS, instead of the actual 43. The same was the case for another smaller table in the old database where the query said 8 rows, but there were 15. (I manually counted multiple times to confirm these two cases)

However, when I ran the same below query on the new, migrated, database as I did on the old database, it was displaying the correct number of rows for those two tables.

SELECT TABLE_ROWS, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE.SCHEMA = 'db_name';

Any thoughts?

Upvotes: 1

Views: 1819

Answers (2)

Gordan Bobić
Gordan Bobić

Reputation: 1888

In order for information_schema to not be painfully slow when retrieving this for large tables, it uses estimates, based on the cardinality of the primary key, for InnoDB tables. Otherwise it would end up having to do SELECT COUNT(*) FROM table_name, which for a table with billions of rows could take hours.

Look at SHOW INDEX FROM table_name and you will see that the number reported in information_schema is the same as the cardinality of the PK.

Running ANALYZE TABLE table_name will update the statistics which may make them more accurate, but it will still be an estimate rather than just-in-time checked row-count.

Upvotes: 2

YudhiWidyatama
YudhiWidyatama

Reputation: 1704

Reading the documentation: https://dev.mysql.com/doc/refman/8.0/en/tables-table.html

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.

Were there any error/warning in the migration log? There are so many ways to migrate mysql table data, I personally like to use mysqldump and importing the resuting sql file using mysql command line client. In my experience importing using GUI clients always have some shortcomings.

Upvotes: 1

Related Questions