11

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 (~):

See image

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.
vindia
  • 1,678
  • 10
  • 14
Saul Martínez
  • 920
  • 13
  • 28

3 Answers3

12

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.

bencoder
  • 836
  • 5
  • 13
  • Thank you bencoder, so I'll check if it's convenient to change the storage engine. I appreciate your help. – Saul Martínez Nov 15 '11 at 15:08
  • You're welcome. I can't see why that's necessary though. Are you relying on this output from phpmyadmin for something? It would probably be much better to write a specific page that gives you the correct value using count(*) – bencoder Nov 15 '11 at 15:14
  • Don't draw the incorrect conclusion. Your setup is not broken, and there is no need to change the storage engine. – Gustav Bertram Nov 15 '11 at 15:18
  • Not really, so, in the future I might need to use fulltext search so leaving that type would be a better approach. – Saul Martínez Nov 15 '11 at 15:22
  • 1
    My suggestion is to convert the table when you need to, in accordance with the [YAGNI](http://en.wikipedia.org/wiki/You_ain%27t_gonna_need_it) principle. – Gustav Bertram Nov 15 '11 at 15:35
  • Note that their is a reason that InnoDB does this, and it makes it better at other things. – Jonathon May 03 '13 at 00:37
7

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

Gustav Bertram
  • 14,591
  • 3
  • 40
  • 65
1

What are the table types? MyISAM tables store an exact row count in their metadata. InnoDB tables can't, as the exact row count varies depending on what transactions are in "in flight", so the count shown for those is marked as an estimate (~ = "approximately") instead of a "it is exactly this many".

Marc B
  • 356,200
  • 43
  • 426
  • 500