List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:March 26 2007 10:42pm
Subject:RE: Why doesn't the InnoDB count() match table status?
View as plain text  
> 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.  :-\


Thread
Why doesn't the InnoDB count() match table status?Daevid Vincent26 Mar
  • Re: Why doesn't the InnoDB count() match table status?Dan Nelson27 Mar
    • RE: Why doesn't the InnoDB count() match table status?Daevid Vincent27 Mar
      • Re: Why doesn't the InnoDB count() match table status?Dan Nelson27 Mar
        • RE: Why doesn't the InnoDB count() match table status?Daevid Vincent27 Mar
          • RE: Why doesn't the InnoDB count() match table status?Wm Mussatto27 Mar
          • Re: Why doesn't the InnoDB count() match table status?Jeremy Cole27 Mar
      • Re: Why doesn't the InnoDB count() match table status?Daniel Kasak27 Mar
        • RE: Why doesn't the InnoDB count() match table status?Daevid Vincent27 Mar
        • Re: Why doesn't the InnoDB count() match table status?Maciej Dobrzanski27 Mar
          • RE: Why doesn't the InnoDB count() match table status?Tim Lucia27 Mar
            • Re: Why doesn't the InnoDB count() match table status?Jochem van Dieten27 Mar