MySQL - Fastest way to check if data in InnoDB table has changed

Question

My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance.

My application does about 500-1000 "CHECKSUM TABLE" statements per second in peak times, because the clients GUI is polling the database constantly for changes (it is a monitoring system, so must be very responsive and fast).

With MyISAM, there are Live checksums that are precalculated on table modification and are VERY fast. However, there is no such thing in InnoDB. So, CHECKSUM TABLE is very slow...

I hoped to be able to check the last update time of the table, Unfortunately, this is not available in InnoDB either. I'm stuck now, because tests have shownn that the performance of the application drops drastically...

There are simply too much lines of code that update the tables, so implementing logic in the application to log table changes is out of the question...

The Database ecosystem consists of one master na 3 slaves, so local file checks is not an option. I thought of a method to mimic a checksum cache - a lookup table with two columns - table_name, checksum, and update that table with triggers when changes in a table occurs, but i have around 100 tables to monitor and this means 3 triggers per table = 300 triggers. Hard to maintain, and i'm not sure that this wont be a performance hog again.

So is there any FAST method to detect changes in InnoDB tables?

Thanks!

Answer 1

The simplest way is to add a nullable column with type TIMESTAMP, with the trigger: ON UPDATE CURRENT_TIMESTAMP.

Therefore, the inserts will not change because the column accepts nulls, and you can select only new and changed columns by saying:

SELECT * FROM `table` WHERE `mdate` > '2011-12-21 12:31:22'

Every time you update a row this column will change automatically.

Here are some more informations: http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

To see deleted rows simply create a trigger which is going to log every deletion to another table:

DELIMITER $$
CREATE TRIGGER MyTable_Trigger
AFTER DELETE ON MyTable
FOR EACH ROW
BEGIN
    INSERT INTO MyTable_Deleted VALUES(OLD.id, NOW());
END$$