36

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

What can cause this?

enter image description here

enter image description here

Server version: 5.1.63-cll
Engine: InnoDB
dtbarne
  • 8,110
  • 5
  • 43
  • 49
Mohammad Ali Akbari
  • 10,345
  • 11
  • 44
  • 62

3 Answers3

59

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”.

WillP
  • 127
  • 1
  • 13
drew010
  • 68,777
  • 11
  • 134
  • 162
  • 13
    Wait the important point is **how** does `SHOW TABLE STATUS` do its estimate? – Pacerier Apr 09 '15 at 14:49
  • but the phpmyadmin GUI could have a shortcut button for the exact count: `SELECT COUNT(*) FROM 'my_table'` – João Pimentel Ferreira Mar 05 '20 at 10:35
  • I encountered this issue as well. i.e. running `SELECT COUNT(*) FROM `table_name`` returns a different value to the value shown when returning the table view in InnoDB. Is there a shortcut in PhpMyAdmin for an actual count? – Luke Galea Oct 06 '20 at 09:12
8

phpMyAdmin uses a quick method to get the row count, and this method only returns an approximate count in the case of InnoDB tables.

See $cfg['MaxExactCount'] for a way to modify those results, but this could have a serious impact on performance.

Community
  • 1
  • 1
SCC
  • 509
  • 7
  • 13
-2

Give column the primary key INT UNSIGNED AUTO_INCREMENT. Then it started showing actual number of rows.

Saral
  • 1,087
  • 1
  • 8
  • 18