List:General Discussion« Previous MessageNext Message »
From:Jeremy Cole Date:March 26 2007 11:52pm
Subject:Re: Why doesn't the InnoDB count() match table status?
View as plain text  
Hi Daevid,

Ugh.  How about not going berserk on the public mailing list?

We can understand that you're upset that you didn't read the manual 
before starting a MyISAM to InnoDB conversion.  You didn't do your 
research and now you're being hit by a very simple (and not really all 
that unexpected) difference between storage engines.

>> You're about 5 years too late for this converation, but I recall it
> 
> Really? People have just happily accepted this absurd limitation for _five_
> years? Wow.

Yes.  And it's not likely to change for a long time, either.

>> 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.
> 
> You know how many are *IN* the table on the disk at that particular moment.
> That's all that needs to be shown!?
> So if someone isn't using transactions, then that number will be accurate.
> This isn't rocket science.

This actually has a lot less to do with transactions, and more to do 
with multiversioning.  The number of rows can and will be different 
within different sessions and there is no trivial way to keep a simple 
count up to date efficiently.

And, if you are using a transactional storage engine, there is no such 
thing as "not using transactions".  Even if you don't use BEGIN/COMMIT 
there are still implicit transactions for each statement.  That's the 
whole point.

Regards,

Jeremy

-- 
high performance mysql consulting
www.provenscaling.com
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