How to quickly checksum a MySQL InnoDB table with millions of records

Question

I have InnoDB based tables that I can checksum with:

checksum table _table_name_

That is slow in case millions of records reside in the table. I wonder if there is any way to recognize, whether a table was altered or not?

I needed something that is fast and cheap to request, and changes every time a change is made to any of the records of a table. I do not find anything for InnoDB tables. Is there something?

Regards Felix

Answer 1

Later versions of MySQL stores a timestamp for each update to the table:

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'dbname'
   AND TABLE_NAME = 'tabname'

Answer 2

If you have a timestamp (say LastUpdateTS) on the table, created with default settings, and an index on it, then this should be rather fast. It will catch only Inserts and Updates but not Deletes though:

SELECT MAX(LastUpdateTS)
FROM TableX

From MySQL Docs, Timestamp Properties:

In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:

With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.

---

If you want to catch any change in the table (Deletes included), a trigger may be the best solution. Every time an INSERT, UPDATE or DELETE statement acts on the table, the trigger can update an auxilary table.