Re: Table # of rows changing?
Hello. I've found a good answer of Heikki Tuuri: InnoDB does not keep accurate row counts. They are only estimates based on 10 random dives into the clustered index tree. See: http://dev.mysql.com/doc/mysql/en/InnoDB_restrictions.html Jeff Burgoon [EMAIL PROTECTED] wrote: I have table with 83,065 rows. Each time I go to MySQL Administrator and look at the catalogs, the number of Rows reported by the administrator changes. I can keep clicking refresh and the number of rows fluctuates between roughly 81,000 and 86,000. I also see similar behavior when I access the table through PHPMyAdmin in browse mode. The crazy part is, this is a static table. I am 100% positive the table is not being inserted to or deleted from. Here is the create statement for my table. Can anybody offer any insight? CREATE TABLE MyTable (Category varchar(100), Sub_Category varchar(100), Part_Number varchar(40), Description varchar(100), Service_Category varchar(10), Price integer, INDEX(Part_Number(10))) type=InnoDB; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table # of rows changing?
I have table with 83,065 rows. Each time I go to MySQL Administrator and look at the catalogs, the number of Rows reported by the administrator changes. I can keep clicking refresh and the number of rows fluctuates between roughly 81,000 and 86,000. I also see similar behavior when I access the table through PHPMyAdmin in browse mode. The crazy part is, this is a static table. I am 100% positive the table is not being inserted to or deleted from. Here is the create statement for my table. Can anybody offer any insight? CREATE TABLE MyTable (Category varchar(100), Sub_Category varchar(100), Part_Number varchar(40), Description varchar(100), Service_Category varchar(10), Price integer, INDEX(Part_Number(10))) type=InnoDB; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table # of rows changing?
It's because the table type is InnoDB. InnoDB tables can only give and estimate of how many rows are in the table. That's just the way InnoDB works. From the manual: SHOW TABLE STATUS does not give accurate statistics on InnoDB tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization. On Nov 16, 2004, at 11:58 AM, Jeff Burgoon wrote: I have table with 83,065 rows. Each time I go to MySQL Administrator and look at the catalogs, the number of Rows reported by the administrator changes. I can keep clicking refresh and the number of rows fluctuates between roughly 81,000 and 86,000. I also see similar behavior when I access the table through PHPMyAdmin in browse mode. The crazy part is, this is a static table. I am 100% positive the table is not being inserted to or deleted from. Here is the create statement for my table. Can anybody offer any insight? CREATE TABLE MyTable (Category varchar(100), Sub_Category varchar(100), Part_Number varchar(40), Description varchar(100), Service_Category varchar(10), Price integer, INDEX(Part_Number(10))) type=InnoDB; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table # of rows changing?
Thanks. Makes sense ... but strange. Jeff Brent Baisley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] It's because the table type is InnoDB. InnoDB tables can only give and estimate of how many rows are in the table. That's just the way InnoDB works. From the manual: SHOW TABLE STATUS does not give accurate statistics on InnoDB tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization. On Nov 16, 2004, at 11:58 AM, Jeff Burgoon wrote: I have table with 83,065 rows. Each time I go to MySQL Administrator and look at the catalogs, the number of Rows reported by the administrator changes. I can keep clicking refresh and the number of rows fluctuates between roughly 81,000 and 86,000. I also see similar behavior when I access the table through PHPMyAdmin in browse mode. The crazy part is, this is a static table. I am 100% positive the table is not being inserted to or deleted from. Here is the create statement for my table. Can anybody offer any insight? CREATE TABLE MyTable (Category varchar(100), Sub_Category varchar(100), Part_Number varchar(40), Description varchar(100), Service_Category varchar(10), Price integer, INDEX(Part_Number(10))) type=InnoDB; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
