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