Given the following:

• Database mydb
• datadir is /var/lib/mysql

Database with all MyISAM Tables

SELECT * FROM ( SELECT table_name, IFNULL(update_time,create_time) LastTimeTouched FROM information_schema.tables WHERE table_schema = 'mydb' ) A ORDER BY LastTimeTouched LIMIT 20; 

Database with all or some InnoDB Tables

This is impossible to do with innodb_file_per_table disabled because all the files reside in a single system tablespace (ibdata1).

Besides data and index pages, there are other things written in the system tablespace:

• Data Dictionary
• Double Write Buffer
  • Safety Net to Prevent Data Corruption
  • Helps Bypass OS for Caching
• Insert Buffer (Streamlines Changes to Secondary Indexes)
• Rollback Segments
• Undo Logs
• Click Here to see a Pictorial Representation of the system tablespace ibdata1

With innodb_file_per_table disabled, there is no way to tell. As far as I know, the InnoDB Storage Engine does not update the UPDATE_TIME column at all.

With innodb_file_per_table enabled, once an InnoDB table exists outside of ibdata1, you can only check the timestamp of the .ibd from the OS.

NEEDED SCRIPT

The following is a script that can tell you the LastUpdated Time for MyISAM and InnoDB tables together in one Database (provided you are using innodb_file_per_table)

MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" FILEDAT=/tmp/GetFileTimestamps.txt FILESQL=/tmp/GetFileTimestamps.sql DATABASE_TO_SEARCH=${1} cd /var/lib/mysql/${DATABASE_TO_SEARCH} ls -l --time-style=+%s *.[iM][bY][dDI] | awk '{print $7,$6}' > ${FILEDAT} NUM=1 SELECT_TAG="SELECT" HEADER1=" Filename" HEADER2=" LastUpdated" echo "SELECT * FROM (" > ${FILESQL} echo "SELECT TableName,MAX(LastUpdated) LastUpdated" >> ${FILESQL} echo "FROM (SELECT REPLACE(REPLACE(REPLACE(FileName,'.ibd','')" >> ${FILESQL} echo ",'.MYD',''),'.MYI','') " >> ${FILESQL} echo "TableName,LastUpdated FROM (" >> ${FILESQL} for TAG in `cat ${FILEDAT}` do (( NUM = 1 - NUM )) if [ ${NUM} -eq 0 ] ; then FILENAME=${TAG} ; fi if [ ${NUM} -eq 1 ] then UNIXTIME=${TAG} SQLSTMT="${SELECT_TAG} '${FILENAME}'${HEADER1}," SQLSTMT="${SQLSTMT} FROM_UNIXTIME(${UNIXTIME})${HEADER2}" echo ${SQLSTMT} >> ${FILESQL} SELECT_TAG="UNION SELECT" HEADER1="" HEADER2="" fi done echo ") AAA) AA GROUP BY TableName) A ORDER BY LastUpdated" >> ${FILESQL} mysql ${MYSQL_CONN} --table < ${FILESQL} 

This script is designed to check the timestamp of every table. The only parameter the table needs is the database.

• For MyISAM, it uses the more recent timestamp of one of the following:
  • .MYD file
  • .MYI file
• For InnoDB, it uses the .ibd timestamp