Just to contribute a different point of view, let me tell you what happens in DB2.
DB2 has positions in its catalog, also known as meta data, to keep all kinds of statistics about the data, including the number of rows in each table. However, the designers made an executive decision in the early days of DB2 that those statistics would NOT be dynamically maintained. Apparently, they feared that they would add a great deal of overhead to DB2 if it constantly updated statistics like the number of rows in each table, the range of values in each column of an index, the number of pages of data in the tablespace, etc. etc. etc. Instead, the provided a utility called RUNSTATS that could be run whenever the user liked. When executed, RUNSTATS refreshes all of the desired statistics for a particular table or index and brings them right up to date. The DB2 Optimizer, the component that chooses access paths for the data, then uses those statistics. (Of course, if you don't run RUNSTATS, the Optimizer uses the existing statistics, whatever they may be. Those statistics may be wildly inaccurate and can skew the access path selection process.) Now, with respect to getting decent performance from a COUNT(*) query, DB2 is quite smart: if there is an index on any column of the table, DB2 can simply go to the index, which is organized as a b-tree, and count the number of index entries. That tends to involve minimum I/O and is therefore frequently very fast. If there are multiple indexes on the table, it may even be able to reason out which index has the fewest number of entries to minimize the amount of counting it has to do but I'm not sure about that. Maybe the MySQL developers will want to think about using techniques like those I've just mentioned for getting a COUNT(*) result faster? Rhino ----- Original Message ----- From: "sheeri kritzer" <[EMAIL PROTECTED]> To: "Kevin Burton" <[EMAIL PROTECTED]> Cc: <mysql@lists.mysql.com> Sent: Monday, November 14, 2005 5:10 PM Subject: Re: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB? Hi all, I know I'm a bit late in coming to this discussion. Glad to see that this problem is on the InnoDB to-do list. I will put out that one thing you can do is utilize triggers. Make a separate table with one field, and put a trigger on the table you want counted so that every time there is an insert to the table, you increment the field in the 2nd table. Add a second trigger to decrement the 'counter table' field every time a delete statement is issued. This should work. It's a hack, but if you need that implementation quickly, there ya go. Granted, you need MySQL 5.0. -Sheeri On 11/1/05, Kevin Burton <[EMAIL PROTECTED]> wrote: > 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 > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
