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