CODEWITHSUNDEEP
mysqlsql
Soroush Rabiei
Soroush Rabiei

Reputation: 10868

Number of records in a MySQL database

Well, this should be enough to get number of all records in current database:

SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE();

Surprisingly, I'm getting different numbers each time I execute above statement! First I thought there is a problem with my database. But it's same for sample db provided by MySQL developers.

Executing above statement on the sakila multiple times results in this values:

46362
48104
45170
47060
48139

What I'm doing wrong? Is this a bug?

Upvotes: 3

Views: 67

Answers (1)

Rich Bradshaw
Rich Bradshaw

Reputation: 72975

This is an InnoDB thing – you can instead count(id) on the table, but it's slow.

Because it's slow, InnoDB takes a random sample of rows, measures the sizes, then divides the total size to determine approx how many rows there are.

Upvotes: 6

Related Questions