> In the last episode (Mar 26), Daevid Vincent said:
> > Aside from the incredibly annoying fact that InnoDB tables
> don't store a
> > total COUNT(), my question is... Why are these numbers
> different? I could
> > easily parse out the second query which is REDICULOUSLY
> faster. BTW, why
> > doesn't mySQL just 'alias' the first query behind the
> scenes for us and
> > parse out the count?
>
> SHOW TABLE STATUS simply returns a guess based on some index dives on
> InnoDB tables, and will actually change every time you run the command
> as it randomly picks different parts of the index to examine.
>
> http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html :
>
> Rows
>
> The number of rows. Some storage engines, such as MyISAM,
> store the
> exact count. For other storage engines, such as InnoDB, this value
> is an approximation, and may vary from the actual value by as much
> as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an
> accurate count.
Thanks Dan.
UGH. This is useless.
WHY doesn't COUNT(*) return fast like MYISM tables do?
This seems like such a glaring oversight when designing INNODB tables.
I don't use transactions. I could give a shit about transactions. I only use
INNODB for Foreign Keys. Can't there be some setting in my.cnf for users
like us?
Is mySQL planning on fixing this BUG. YES -- it is a BUG. A BIG FAT HARRY
ONE.
It's completely stupid that I can't query and get an accurate total of
records in a table in under a second.
A deviation of 40-50% is SIGNIFICANT!
You might as well just use RAND() at that point.
Oh, and BTW, my company DOES pay mySQL for a support contract of several
thousand dollars per year. So spare me the 'free software' speeches...
This is absolutely baffling and astonishing how a company so seemingly smart
could make such an incredible blunder. :-\
