I think I can summarize what has been explained before: 1) InnoDB does not always "know" exactly how many rows are in the database from the point of view of each user. This is a side effect of the row-level locking system. Each user has the potential to "see" a different set of rows depending on the table status and what is going on at the moment.
2) To compensate for this inexact number, SHOW TABLE STATUS performs 10 random dives into the table tree and the estimate you see is the average of those 10 dives. This saves time because you don't have to physically determine each and every row that is visible to the user. 3) Count(*) will perform that physical count but it takes much longer because it MUST perform a table scan in order to determine which row is visible to the user (the # of committed rows not part of another user's transaction plus the # of rows added as part of the current user's transaction). Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Duhaime Johanne" <[EMAIL PROTECTED]> wrote on 01/17/2005 01:10:45 PM: > Thank for your answer. I did a "show table status" and this is > convenient for what I want. > > But I was surprised that two of the 26 tables give a row number > different each time I process a "show table status". The size > remains the same. > > In a 3 times "show table status" I got 699-634-731 rows. A select > from the table give 677 rows. > > > Here is how the table is created. > > What can explain that? > > Johanne Duhaime > > > > > > > CREATE TABLE `Alias` ( > `oid` bigint(20) NOT NULL auto_increment, > `aliasId` varchar(100) NOT NULL default '', > `proteinOid` bigint(20) NOT NULL default '0', > `aliasGroupOid` bigint(20) NOT NULL default '0', > `description` varchar(255) default NULL, > `sourceDatabase` varchar(100) default NULL, > `determined` tinyint(4) NOT NULL default '0', > `sequence` text, > PRIMARY KEY (`oid`), > UNIQUE KEY `aliasId_2` (`aliasId`), > KEY `aliasId` (`aliasId`), > KEY `proteinOid` (`proteinOid`), > KEY `aliasGroupOid` (`aliasGroupOid`), > CONSTRAINT `Alias_ibfk_1` FOREIGN KEY (`proteinOid`) REFERENCES > `Protein` (`oi > d`) ON UPDATE CASCADE, > CONSTRAINT `Alias_ibfk_2` FOREIGN KEY (`aliasGroupOid`) REFERENCES > `AliasGroup > ` (`oid`) ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > > -- > -----Message d'origine----- > De : Jeff Mathis [mailto:[EMAIL PROTECTED] > Envoyé : 14 janvier 2005 16:15 > À : Duhaime Johanne > Cc : mysql@lists.mysql.com > Objet : Re: actual size of a innodb tablespace > > if you issue a "show table status" command from the mysql prompt, > you'll get an estimate of how much free space exists in the files. > > > Duhaime Johanne wrote: > > Hello > > > > Context: innodb per table. > > > > Is it possible to know the actual size of my innodb file? What part of > > the initial size (10M: autoextend) is actually used? I would like to > > have an idea of the data space progression? Per database (I have 3). > > > > Thank you in advance > > > > Johanne Duhaime > > IRCM > > courrier: [EMAIL PROTECTED] > > > > > > > > > -- > Jeff Mathis, Ph.D. 505-955-1434 > Prediction Company [EMAIL PROTECTED] > 525 Camino de los Marquez, Ste 6 http://www.predict.com > Santa Fe, NM 87505 > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
