MySQL: Show count of records in each table of a database

December 12, 2012

I recently did a dump of my database on this site and it was almost 300 megabytes! That is pretty huge for a simple personal site. Curious as to where all that data was hiding I whipped up a quick SQL query that calculates the total number of rows per table in a single database. Here is the query for your enjoyment ( and so I remember it later! )

WHERE TABLE_SCHEMA = 'db21288_ben' 

3 thoughts on “MySQL: Show count of records in each table of a database

  1. phinhathai (April 27, 2016)

    I found this result row not the same as select count(*) from table_name; some difference about 87,xxx rows.

    1. Ben Lobaugh (blobaugh) (April 27, 2016)

      Are you on a cluster? Could be a sync issue. The information_schema should be updates frequently enough that you will not see disparate counts, however if you are on a cluster that needs to sync or there is a hangup in MySQL’s internal processes the count could be off. Try it again, is it still off?

  2. Ivan (June 8, 2016)

    This query gives rough estimate on innodb databases. It is not precise and this is why you get different values when you do COUNT(*)