No. of records in a table

[expired user #5580]'s profile image [expired user #5580] posted 13 years ago in General Permalink
Sir
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
ansgar's profile image ansgar posted 13 years ago Permalink
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
kalvaro's profile image kalvaro posted 13 years ago Permalink
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.