Number of rows in table information incorrect

[GoogleCodeExporter]

I've only noticed this happening on tables that are relatively large--maybe 
100,000+ rows.

I would expect to see the same value as would be returned by a SELECT COUNT 
query on the table, but instead I see some number that's usually reasonably 
close, but not actually the correct number. If you hit the refresh button, 
it'll produce another seemingly random number (keep clicking refresh and you'll 
keep getting different numbers).

What version of Sequel Pro are you using? What version of MySQL are you
connecting to on the server?
Sequel Pro: 0.9.9.1, MySQL: 5.1.5

Original issue reported on code.google.com by modemmon...@gmail.com on 14 Sep 2011 at 3:41

[GoogleCodeExporter]

Hi there,

Is this for InnoDB tables?  If so, the number should be shown with a "~" before 
it, to indicate it's an approximation.

InnoDB tables don't keep an accurate count of the rows inside them.  Instead, 
when something like a SHOW TABLE STATUS is used, the MySQL server quickly 
calculates an approximate number of rows based on the total table size and 
average row size.  (I have no idea why this number varies so much when 
refreshed).

We can request an accurate row number, using something like the SELECT COUNT, 
but due to the way InnoDB tables store their rows, this requires a scan - 
either an index scan if available (usually fast), or a table scan if now 
(slow).  For large tables in active use, this is a Bad Thing :)

You'll probably notice your SELECT COUNT query takes a non-trivial time to 
complete - this is why.

Hope that helps explain why; I'm going to mark this as "WontFix" as a result, 
but if you have further feedback, do add a comment - we still see the responses.

Original comment by rowanb@gmail.com on 14 Sep 2011 at 3:55

• Changed state: WontFix