MySQL Query to find tables modified in the last hour?

You can achieve this with the help of INFORMATION_SCHEMA.TABLES. Use the date_sub() with interval. The syntax is as follows −

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE DATE_SUB(NOW(), INTERVAL -1HOUR) < ‘UPDATE_TIME’;

Now you can check the above syntax. Here is the query to find the tables modified in the last hour −

mysql> select table_name from `INFORMATION_SCHEMA`.`TABLES`
-> WHERE
-> DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`;

Output

+---------------------+
| TABLE_NAME          |
+---------------------+
| innodb_table_stats  |
| innodb_index_stats  |
| employeeinformation |
+---------------------+
3 rows in set (0.37 sec)

The above query selects only table name. If you want information like table schema, table type etc, you need to use the following query. The query displays the table modified in the last hour −

mysql> SELECT *
-> FROM `INFORMATION_SCHEMA`.`TABLES`
-> WHERE
-> DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`;

The following is the output displaying the table information modified in the last hour −

+---------------+--------------+---------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME         | TABLE_TYPE  | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+---------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+---------------+
| def           | mysql        | innodb_table_stats  | BASE TABLE | InnoDB | 10      | Dynamic    | 449        | 145            | 65536       | 0               | 0            | 4194304   | NULL | 2018-09-22 20:37:26 | 2018-12-24 15:13:41 | NULL | utf8_bin | NULL |
row_format=DYNAMIC stats_persistent=0 | |
| def           | mysql        | innodb_index_stats  | BASE TABLE | InnoDB | 10      | Dynamic    | 1413       | 243            | 344064      | 0               | 0            | 4194304   | NULL | 2018-09-22 20:37:26 | 2018-12-24 15:13:41 | NULL | utf8_bin | NULL |
row_format=DYNAMIC stats_persistent=0 | |
| def           | test         | employeeinformation | BASE TABLE | InnoDB | 10      | Dynamic    | 6          | 2730           | 16384       | 0               | 0            | 0         | NULL | 2018-12-24 15:12:04 | 2018-12-24 15:14:00 | NULL | utf8mb4_0900_ai_ci | NULL | |
|
+---------------+--------------+---------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+---------------+
3 rows in set (43.72 sec)

Ankith Reddy

Published on 10-Jan-2019 12:07:20

Previous Page Print Page