In the last episode (Mar 26), Daevid Vincent said:
> > 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.
> 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.
You're about 5 years too late for this converation, but I recall it
having to do with the fact that when you're on a table that supports
transactions, you don't know exactly how many records a particular
session has available to it unless you actually go and count them.
Depending on your settings, you may or may not see rows inserted by
other uncommitted sessions, and they may disappear if the other
sessions roll their transactions back.
> 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'
You should probably be filing bug reports or calling your support
number then, not sending emails to the free-support mailing list.
Maybe ask if they could implement foreign keys for MyISAM instead of
asking for Innodb to do something it may not be able to do.
> This is absolutely baffling and astonishing how a company so
> seemingly smart could make such an incredible blunder. :-\
Let us know if you find another database product that supports instant
count(*)'s on transactioned tables.