Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

When I execute below query from MySQL server, it shows me different output for each time even though database is stable and not used by anyone except me.

SELECT TABLE_NAME , table_rows
FROM information_schema.tables 
WHERE table_schema='DBNAME' 
AND TABLE_TYPE != 'VIEW'

MySQL Server Version: 5.1

I am quite confused with this behaviour.

share|improve this question
1  
Add different result in your question so we can find that what the peoble is – Saharsh Shah Nov 21 '12 at 7:08

1 Answer

up vote 3 down vote accepted

I'm guessing you're getting varying row count in the table_row column? This is expected behaviour for InnoDB-tables.

"For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned."

http://dev.mysql.com/doc/refman/5.1/en/tables-table.html

There is more information here: http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html

Notably: "ANALYZE TABLE determines index cardinality (as displayed in the Cardinality column of SHOW INDEX output) by doing eight random dives to each of the index trees and updating index cardinality estimates accordingly. Because these are only estimates, repeated runs of ANALYZE TABLE may produce different numbers. This makes ANALYZE TABLE fast on InnoDB tables but not 100% accurate because it does not take all rows into account"

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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