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

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.

-- 
	Dan Nelson
	dnelson@stripped
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