No. of records in a table

User, date Message
Written by jothimanin
4 years ago
Category: General
2 posts since Tue, 08 Mar 11
after opening a database heidisql.gui display no. of records in each table.
This display(No. of records in in each table) changes whenever I press refresh button, even though there are no transaction made. Why?

Why con't Heidi dispaly correct no. of records in a table at any moment ?

thanks in advance
Jothimani N
Written by ansgar
4 years ago
5045 posts since Fri, 07 Apr 06
This is not HeidiSQL, but the table engine InnoDB which reports its row number as an estimation. See http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html
Written by kalvaro
4 years ago
601 posts since Thu, 29 Nov 07
Calculating the exact row number for InnoDB tables requires issuing a SELECT COUNT(*) FROM table query for each existing table. Such queries can take a long time. Quoting from the link posted by Anse:

InnoDB does not keep an internal count of rows in a table. (In practice, this would be somewhat complicated due to multi-versioning.) 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. SHOW TABLE STATUS also can be used if an approximate row count is sufficient.

Please login to leave a reply, or register at first.