tag:blogger.com,1999:blog-18337119.post115379650111602604..comments2024-01-22T07:09:30.574-05:00Comments on MySQL Consulting and NoSQL Consulting: MySQL DBA: Where did the records go?Unknownnoreply@blogger.comBlogger4125tag:blogger.com,1999:blog-18337119.post-1153842142110707142006-07-25T11:42:00.000-04:002006-07-25T11:42:00.000-04:00Thanks Peter.But that still doesn't explain why r...Thanks Peter.<BR/><BR/>But that still doesn't explain why running COUNT(*) on MyISAM returns 47000000 and running COUNT(*) on InnoDB (after conversion) returns 19000000 records. Any ideas?Frankhttps://www.blogger.com/profile/04086666898806120300noreply@blogger.comtag:blogger.com,1999:blog-18337119.post-1153830571826875112006-07-25T08:29:00.000-04:002006-07-25T08:29:00.000-04:00Hi, check the manual (the "rows" section): http://...Hi, check the manual (the "rows" section): http://dev.mysql.com/doc/refman/4.1/en/show-table-status.htmlAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-1153824267038112032006-07-25T06:44:00.000-04:002006-07-25T06:44:00.000-04:00InnoDB doesn't keep a row count in the table itsel...InnoDB doesn't keep a row count in the table itself, as MyISAM does. This is because of transactional concurrency requirements. Therefore, count(*) works differently; in MyISAM, it can just ask the table how many rows it has, but in InnoDB, it has to scan some index and find out. This is slow. For that reason, the SHOW TABLE STATUS value is only an estimate, and can be very wrong, in InnoDB.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-1153818739109524452006-07-25T05:12:00.000-04:002006-07-25T05:12:00.000-04:00Frank, "Rows" in SHOW TABLE STATUS is estimtion o...Frank, <BR/><BR/>"Rows" in SHOW TABLE STATUS is estimtion only for Innodb tables which is done using random dives to btree each time it is requested, so it can fluctuate a lot over time.Anonymousnoreply@blogger.com