From: Dan Nelson
Date: February 21 2008 4:59pm
Subject: Re: Number of rows not constant
List-Archive: http://lists.mysql.com/mysql/211465
Message-Id: <20080221165953.GA3328@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Feb 21), Mike Spreitzer said:
> I have a table with millions of rows.  I am not sure exactly how many rows 
> it has, I get a different answer every time I ask!  What's going on here?
> 
> This DB is used only by me, and only by explicit commands --- I have no 
> background or on-line tasks using the DB.  This is the DB that took 2 days 
> to load and another two days to add a column and index.  I decided to let 
> that column+index addition to complete, and it has now completed.  I am 
> using the GUI administrator tool; in the Catalogs section I select the 
> relevant schema; in the right hand side I select the Tables tab.  The 
> listing for my table (I have only the one) says Type=InnoDB, Row Format = 
> Compact, Data Length = 4.56 G, Index Length = 8.55 G, and Update Time is 
> blank.  It is the Rows datum that is surprising --- every time I hit 
> "Refresh" I get a different number under Rows.  It varies between 23 
> million and 28 million.  It is not monotonically increasing, nor 

That's not an actual row count; it's simply a guess.  InnoDB tables
have to walk the entire table to get a row count, so in the "show table
status" output mysql just makes a guess based on a few random index
lookups.  If you must know the exact number of records, use "select
count(*) from mytable", but expect it to take a minute or so.

-- 
	Dan Nelson
	dnelson@stripped