title: How can I determine when an InnoDB table was last changed?
body: <p>I've had success in the past storing the (heavily) processed results of a database query in memcached, using the last update time of the underlying tables(s) as part of the cache key.  For MyISAM tables, that last changed time is available in <code>SHOW TABLE STATUS</code>.  Unfortunately, that's usually NULL for InnoDB tables.</p>
  
  <p>In MySQL 4.1, the ctime for an InnoDB in its <code>SHOW TABLE STATUS</code> line was usually its actual last update time, but that doesn't seem to be true for MySQL 5.1.</p>
  
  <p>There is a DATETIME field in the table, but it only shows when a row has been modified - it cannot show the deletion time of a row that's not there anymore!  So, I really cannot use <code>MAX(update_time)</code>.</p>
  
  <p>Here's the really tricky part.  I have a number of replicas that I do reads from.  Can I figure out the state of the table that doesn't rely on when the changes have actually been applied?</p>
  
  <p>My conclusion after working on this for a while is that it's not going to be possible to get this information as cheaply as I'd like.  I'm probably going to cache data until the time that I expect the table to change (it's updated once a day), and let the query cache help out where it can.</p>