phpMyAdmin - What a tilde (~) means in rows column?

Question

I recently upgrade a Joomla! installation from 1.5 to 1.7 with a large dataset of articles and while the upgrade method was upgrading the database (inserting content to another table, basically) I noticed that the Rows column value was prefixed with a tilde (~):

Now, at first sight I assume this means that the value is approximate because when I refresh the page I see a different value, sometimes higher, sometimes lower. OK, lets say the value is approximate, in that case, what is causing that? Some considerations:

• OS: Windows 7.
• Server: Apache 2 with PHP 5.3 and MySQL 5.1.50-community.

Answer 1

The row count is an approximation used for query optimisation by InnoDB. doing a count(*) is a lot more inefficient and having a rough idea of the number of rows is sufficient to optimise the query plan. phpMyAdmin will be doing a "show table status" query to get the information about the table:

http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html

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.

Answer 2

According to the MySQL Manual on InnoDB restrictions:

• 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 must scan 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 13.2.13.1, "InnoDB Performance Tuning Tips".

According to the page for SHOW TABLE STATUS, displayed InnoDB rowcounts may vary from the actual count by up to 50%.