Jigal van Hemert <[EMAIL PROTECTED]> wrote on 11/02/2005 03:29:14 AM:
> Shankar Unni wrote: > > [EMAIL PROTECTED] wrote: > > > >> 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. > > > > > > But one thing you can do to speed it up somewhat is to do a > > COUNT(PK_column) (rather than COUNT(*)) if you have a column that is a > > primary key for the table - that's the same thing semantically, and > > involves searching an index rather than the data records themselves, > > which should involve less I/O. > > > If I understand > http://dev.mysql.com/doc/refman/5.0/en/table-and-index.html > correctly, the index of the primary key is stored as the clustered index > together with the data. To me this means that there is no difference > between counting the primary key entries and counting the data entries. > > Regards, Jigal. > That would be true if everyone could always see every record. However there could be several active copies (versions) of any record. Each copy would be part of a different transaction. How many records YOU can see can be very different than how many records I can see depending on what's going on in our respective transactions. Consider the following time line: The database starts with 1000 records in it I start a transaction You start a transaction I delete 50 records You add 50 records I do a COUNT(*) and see 950 records. You do a COUNT(*) and see 1050 records. I commit my transaction - database now has 950 records to everyone but you. You commit your transaction - database has 1000 records again. How InnoDB keeps up with which records are "visible" or "modifiable" with respect to any transaction is through row-level locking, transaction isolation levels, and multi-versioning. http://dev.mysql.com/doc/refman/4.1/en/innodb-transaction-model.html http://dev.mysql.com/doc/refman/4.1/en/innodb-multi-versioning.html That is what makes counting how many records each person can see is not so straight-forward. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
