MySQL Forums :: InnoDB :: COUNT(*) on innodb

Jue (Jacky) Shu wrote:
> As the solution using a counter table, that means
> recoding in each INSERT/DELETE for each table.

Well, you *could* upgrade to MySQL 5 and use triggers! That would save you a LOT of work... :)

> I'm evaluating InnoDB storage engine because of
> lacking for referential integrity, it is too hard
> to keep data clean.
> Also in the replicatoin scenario, if a job on
> master is killed, there is no way to know how much
> rows are affected in the previous job. The log on
> slave can only tell me the job is killed.

I understand. You're in the same boat as a number of people. This restriction on InnoDB tables has persisted for quite some time; I'm not sure if there are current plans to look into a method of storing row counts for fast access (Heikki, perhaps?) But I can understand why it's complicated, as each InnoDB row is "versioned" using MVCC, so row counts would need to be isolation-level accurate. This makes things very complex...

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com