Kevin Burton <[EMAIL PROTECTED]> wrote on 11/01/2005 03:39:59 PM: > MyISAM has a cool feature where it keeps track of the internal row > count so that > > SELECT COUNT(*) FROM FOO executes in constant time. Usually 1ms or so. > > The same query on INNODB is O(logN) since it uses the btree to > satisfy the query. > > I believe that MyISAM just increments an internal count so that every > insert/delete changes the count. > > Are there plans to add this to INNODB? I was relying on this for my > database monitoring app and noticed that it was killing my > performance (I forgot about this problem...) > > Would be really nice to have. > > Kevin > > Kevin A. Burton, Location - San Francisco, CA > AIM/YIM - sfburtonator, Web - http://www.feedblog.org/ > GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 > > If I understand the InnoDB engine correctly, I don't see how they could speed it up unless they start tracking how many records belong to each active "version" within a database.
Look at it this way. I may be able to see 1000 records and you may be able to see 1000 records but nothing guarantees that we are looking at the SAME 1000 records in the table due to transactional boundaries and row versioning. If, for example, the InnoDB engine tracked that 800 rows are version a, 200 are version b, and 200 are version c it would be linear (based on how many active versions there are) to determine how many rows of each version is visible to each user's transaction. The problem is, each and every row can have a different version number. There would be no advantage to storing and maintaing a list of single-entry version totals over actually crawling the database whenever you needed to identify which rows are visible to each user at any one time. I am sure if you can design an efficient method of tracking number-of-records-by-version, Heikki would probably be glad to hear of it as this has been a complaint for some time. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
