Row count in database view is incorrect

[expired user #9113]'s profile image [expired user #9113] posted 9 years ago in General Permalink
Hello ALL,

HeidiSQL displays row count, for each table in a database, when I open database view, in the right panel. In that panel HeidiSQL shows rows. The row count displayed there is incorrect.

It wreaks havoc with my work.

For e.g., I was making a monitoring script for announcements. Now, HeidiSQL is showing 93 rows in the table, where-as for a query I was running, I was getting more than 150 rows in the resultset. After wasting some time on Freenode, trying to figure out why there was a row count difference, when I finally ran the select count(*) from table query in the query tab, I found out, that my query was ok. HeidiSQL was showing wrong row count for that table.

Please kindly fix the incorrect row count displayed in database view, where it lists all the tables in a database


Best Regards,
M. Istehbab
ansgar's profile image ansgar posted 9 years ago Permalink
Welcome to the estimated row count in InnoDB tables.
arucard's profile image arucard posted 9 years ago Permalink
Hi, if you are looking at tables list on tab 'Database: xxx', you actually see result of this query:
SHOW TABLE STATUS FROM `<your database name>`;


And, if is your table on InnoDB engine, you get misleading information. MySQL does not have accurate statistics for InnoDB engines, see documentation.

Quote: "... 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. ..."

Therefor only running
SELECT COUNT(1) FROM <your table name>
gives you correct number.

Regards
arucard
arucard's profile image arucard posted 9 years ago Permalink
Ah, ansgar was faster. :)

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