There are two distinct answers I have to give by Storage Engine
MyISAM
For all MyISAM tables, checking the last time a table was updated is quick and dirty
SET @IntervalMinutes = 2;
SET @IntervalSeconds = @IntervalMinutes * 60;
SET @DBtoCheck = DATABASE();
SELECT table_schema,table_name,update_time
FROM information_schema.tables WHERE engine='MyISAM'
AND table_schema = @DBtoCheck
AND update_date > (NOW() - INTERVAL @IntervalSeconds SECOND)
ORDER BY update_time DESC A;
InnoDB
I have some bad news: you cannot trust the INFORMATION_SCHEMA database because
- Transaction Behavior of InnoDB
- Location of the Data and Index Pages
- Heavy duty locking of the INFORMATION_SCHEMA when reading about an InnoDB table
In order to know the last update by some timestamp, you must have innodb_file_per_table enabled. If not, all the data and index pages will sit inside ibdata1 (system tablespace). It would be anybody's guess when a table experienced an update.
You should first cleanup the InnoDB Infrastructure.
Then, the easiest way to get the timestamp is to ask the OS for it
EXAMPLE
Given this scenario
- datadir is
/var/lib/mysql
- you want timetamp of
mydb.mytable
Simply run this in the OS
TS_TAB=`ls -l --time-style="+%s" /var/lib/mysql/mydb/mytable.ibd | awk '{print $6}'`
TS_NOW=`date +%s`
(( TS_DIFF = TS_NOW - TS_TAB ))
TS_DIFF will be the number of seconds elapsed since the last time a write was posted to that table. That write can be anything (INSERT, UPDATE, DELETE, DDL).
That's as fast as I can get it for InnoDB
Give it a Try !!!
