Diamond Notes

Just another WordPress weblog

Tip of the Day — Row Count

February 26th, 2008 | Category: MySQL, Tip of the Day

There are several ways to count rows in a table.

SELECT count(*) from table_name;

or

SHOW TABLE STATUS  LIKE ‘table_name%’;

or

SELECT table_name, table_rows FROM information_schema.tables where table_name=’table_name’ and table_schema=’database_name’;

However, the last two methods of getting a row count are only approximate row counts for Innodb tables.  Keep that in mind.

2 Comments so far

  1. Ronald Bradford February 27th, 2008 7:59 am

    COUNT (*) can be the death of performance if you are not careful.

    MyISAM for example stores an exact count of the rows and can return this immediately.
    Innodb for example has to read the entire table, and if for example the table is 160GB in size and you have say 8GB of innodb_buffer_pool_size it will take a long time to read all the data, and directly affect any other system performance.

    One must be more creative in counting rows in larger systems.

  2. Shantanu Oak February 28th, 2008 9:27 am

    >> However, the last two methods of getting a row count
    >> are only approximate row counts for Innodb tables.

    I learned that only after asking about it on MySQL forums!

Leave a reply