PhpMyAdmin - What does tilde (~) mean in a row column?

Question

I recently updated Joomla! installation from 1.5 to 1.7 with a large set of data, and during the upgrade process there was a database upgrade (inserting content into another table mainly). I noticed that the value of the Row column was the tilde prefix (~):

Now, at first glance, I assume that this means that the value is approximate, because when I refresh the page, I see a different value, sometimes higher, sometimes less. Well, let's say that the value is approximate, in this case, what causes this? Some considerations:

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

Answer 1

A row counter is an approximation used to optimize queries in InnoDB. the execution of the counter (*) is much more inefficient, and in order to optimize the query plan, an approximate idea of ​​the number of rows is sufficient. phpMyAdmin will execute the query "show table status" to get information about the table:

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

For other storage systems such as InnoDB, this value is an approximation and can range from the actual value to as much as 40 to 50%. In such cases, use SELECT COUNT (*) to get an accurate score.

Answer 2

According to the InnoDB Limitations MySQL Guide :

• SHOW TABLE STATUS does not provide accurate statistics on InnoDB tables, except for the physical size reserved for the table. The number of rows is a rough estimate used in SQL optimization.

• InnoDB does not store the internal row count in the table, because concurrent transactions can "see" different numbers of rows at the same time. To process the SELECT COUNT(*) FROM t InnoDB must scan the table index, which takes some time if the index is not completely in the buffer pool. If your table does not change frequently, using the MySQL query cache is a good solution. To get a quick score, you need to use the counter table that you create, and let your application update it according to the inserts and delete it. If an approximate row count is sufficient, you can use SHOW TABLE STATUS . See Section 13.2.13.1, “ InnoDB Performance Tuning Tips ”.

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

Answer 3

What are the types of tables? MyISAM tables store the exact number of rows in their metadata. InnoDB tables cannot, because the exact number of rows varies depending on which transactions are in flight, so the count shown for them is marked as an estimate (~ = "approximately") instead of "this is a lot."