Hi,
On 2/21/08 11:41 AM, "Mike Spreitzer" <mspreitz@stripped> wrote:
> 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
> monotonically decreasing. Sometimes the number of rows goes up, sometimes
> it goes down. BTW, before the late addition of a column+index, the Rows
> datum was 27,413,306. I did not notice this Rows variability before
> adding a column+index --- but probably would not have, I had no reason to
> Refresh the display repeatedly. I first noticed this Rows variability
> during the column+index addition.
>
> I am running MySQL 5.0.51a-community on RHEL 4 on a 4-processor (as far as
> Linux is concerned) Intel 32-bit machine, with storage on the only local
> HDD. I am using MySQL Administrator version 1.2.12, and it also says I am
> using MySQL Client Version 5.0.30. I am running the admin tool on the
> same machine as the MySQL server. That machine is otherwise idle. I
> monitor CPU, network, and disk with Procmeter3, updated every 5 seconds.
> It usually reports 0% CPU and 0 disk I/Os per period, and suitably low
> network traffic. Sometimes I get a spike up to 5 disk I/Os in some 5
> second period, presumably to some background thing(s) Linux and/or MySQL
> is doing. When I hit Refresh, I get 22 or 23 disk I/Os and 2% CPU for
> about 5 seconds.
>
> Thanks,
> Mike
It sounds like the GUI Administrator is using SHOW TABLE STATUS to get the
row count for you, since that's much faster than a count(*).
SHOW TABLE STATUS can only provide an estimate for the innodb row count and
it can vary a lot.
The first two comments here are particularly apropos:
http://mysqldatabaseadministration.blogspot.com/2006/07/where-did-records-go
.html
-Dan
