distal-attribute
distal-attribute
distal-attribute
distal-attribute

Num rows in table keep fluctuating in Heidi only

User, date Message
Written by incursio
4 years ago
Category: General
2 posts since Mon, 15 Nov 10
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?

Written by ansgar
4 years ago
4940 posts since Fri, 07 Apr 06
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

MySQL docs wrote: 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.

Written by incursio
4 years ago
2 posts since Mon, 15 Nov 10
Ah, despite tons of searching on my end, I didn't come across that information. Thanks for sharing :)
Written by klor
10 months ago
3 posts since Mon, 01 Oct 12
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.