MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:February 12 2005 10:14pm
Subject:Re: slow query, how can i imporve it?
View as plain text  
Chris Elsworth wrote:

> On Fri, Feb 11, 2005 at 10:45:46AM -0500, SGreen@stripped wrote:
> 
>>Normally I do not reply to myself but I just realized that in my previous 
>>response I confused COUNT(*) (which is slow for InnoDB because it always 
>>does a table scan to resolve the version lock of each and every row) with 
> 
> Hello all,
> 
> You just reminded me about this, I've been meaning to ask; are there
> any plans to "fix" this for InnoDB? It seems like quite a serious
> omission that InnoDB doesn't keep an accurate internal row count. Are
> there technical reasons why this isn't done, or is it in the TODO for
> any time soon? It's really one of the biggest things stopping me from
> switching wholly to InnoDB :(

Shawn answered this in his previous message.  "InnoDb uses versioning locks 
on it's records, that makes it practically impossible to determine exactly 
how many records are available to any user at any one time.  This improves 
concurrency but makes COUNT(*) hard to compute quickly."  In other words, 
yes, there is a technical reason why an accurate internal row count cannot 
be kept for InnoDB tables, because the answer to COUNT(*) is effectively 
connection specific.  If I have added 12 rows but not yet committed, and 
Shawn has added 23 rows but not yet committed, then you, Shawn, and I each 
get different answers for COUNT(*).

Michael
Thread
slow query, how can i imporve it?Shailendra Soni9 Feb
  • Re: slow query, how can i imporve it?SGreen9 Feb
    • Warning: V3 DSA signature: NOKEY, key ID 5072e1f5Chuck Herrick9 Feb
RE: slow query, how can i imporve it?Jay Blanchard9 Feb
Re: slow query, how can i imporve it?SGreen10 Feb
  • Re: slow query, how can i imporve it?Shailendra Soni11 Feb
    • Re: slow query, how can i imporve it?SGreen11 Feb
      • Re: slow query, how can i imporve it?SGreen11 Feb
        • Re: slow query, how can i imporve it?Chris Elsworth12 Feb
          • Re: slow query, how can i imporve it?Michael Stassen12 Feb
Re: slow query, how can i imporve it?Heikki Tuuri13 Feb