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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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' 

MySQL Server Version: 5.1

I am quite confused with this behaviour.

share|improve this question
Add different result in your question so we can find that what the peoble is – Saharsh Shah Nov 21 '12 at 7:08
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."

There is more information here:

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


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.