mysql - Why is the estimated rows count very different in phpmyadmin results?

Question

Without any changes on database I got very different rows count on my tables.

What can cause this?

Server version: 5.1.63-cll
Engine: InnoDB

Answer 1

Unlike MyISAM tables, InnoDB tables don't keep track of how many rows the table contains.

Because of this, the only way to know the exact number of rows in an InnoDB table is to inspect each row in the table and accumulate a count. Therefore, on large InnoDB tables, performing a SELECT COUNT(*) FROM innodb_table query can be very slow because it does a full table scan to get the number of rows.

phpMyAdmin uses a query like SHOW TABLE STATUS to get an estimated count of the number of rows in the table from the engine (InnoDB). Since it's just an estimate, it varies each time you call it, sometimes the variations can be fairly large, and sometimes they are close.

Here is an informative blog post about COUNT(*) for InnoDB tables by Percona.

The MySQL manual page for SHOW TABLE STATUS states:

The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

The page on InnoDB restrictions goes into some more detail:

SHOW TABLE STATUS does not give accurate statistics on InnoDB tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization.

InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. To process a SELECT COUNT(*) FROM t statement, InnoDB scans an index of the table, which takes some time if the index is not entirely in the buffer pool. If your table does not change often, using the MySQL query cache is a good solution. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. If an approximate row count is sufficient, SHOW TABLE STATUS can be used. See Section 14.3.14.1, “InnoDB Performance Tuning Tips”.