Num rows in table keep fluctuating in Heidi only

incursio posted 6 years ago in General
I'm hoping there is a simple explanation for this, but I'm out of ideas.

Running the latest version of Heidi (v5.1.0.3596). I have an InnoDB table with 437K rows in it. There are no active queries or processes on the server (other than my Heidi connection).

Whenever I look at the database view in Heidi, the table always show up with the rows/size highlighted in green. I assumed this meant that Heidi thinks the table is locked or something.

At any rate, if I reload the view (F5), it still stays green, but the number of rows will fluctuate up or down, although the size will stay the same.

If I manually do a "SELECT COUNT(*)" from this table, it always returns the same value (437,909).

I have restarted the MySQL server, and even enabled query logging to see if some rogue process was writing to the table. But nothing. I have even made a copy of the table, and even it shows up the same way. Bizarre.

There doesn't appear to be a way to attach images here, otherwise I would show you a screenshot.

Any ideas?

ansgar posted 6 years ago
Bizarre but documented - welcome to the contras of InnoDB. You will see this variation when firing a SHOW TABLE STATUS. Which is the same as HeidiSQL does when refreshing the list of tables. In some old release (some years ago) HeidiSQL did a SELECT COUNT(*) for fetching the row count but the InnoDB folks raged against this logic as this made refreshes extremely slow on huge databases. I recall this topic was long discussed and partly difficult to work around at that time. Well, since then we live with the estimated row count returned.

See http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

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.

incursio posted 6 years ago
Ah, despite tons of searching on my end, I didn't come across that information. Thanks for sharing :)
klor posted 3 years ago
Would be possible to show number of records in INNODB using "SELECT COUNT(*)"?
At least optionally.

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