Is there a way to find the least recently used tables in a schema? Is there a way to find the least recently used tables in a MySQL schema? Besides going into data directories? I was hoping there was a metadata or status trick-- but Update_Time in STATUS and INFORMATION_SCHEMA is always NULL.
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}' &gt; ${FILEDAT}
NUM=1
SELECT_TAG="SELECT"
HEADER1=" Filename"
HEADER2=" LastUpdated"
echo "SELECT * FROM (" &gt; ${FILESQL}
echo "SELECT TableName,MAX(LastUpdated) LastUpdated" &gt;&gt; ${FILESQL}
echo "FROM (SELECT REPLACE(REPLACE(REPLACE(FileName,'.ibd','')" &gt;&gt; ${FILESQL}
echo ",'.MYD',''),'.MYI','') " &gt;&gt; ${FILESQL}
echo "TableName,LastUpdated FROM (" &gt;&gt; ${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} &gt;&gt; ${FILESQL}
        SELECT_TAG="UNION SELECT"
        HEADER1=""
        HEADER2=""
    fi
done
echo ") AAA) AA GROUP BY TableName) A ORDER BY LastUpdated" &gt;&gt; ${FILESQL}
mysql ${MYSQL_CONN} --table &lt; ${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