By Arun Bertil


2014-09-17 10:16:02 8 Comments

I have a lot of tables in my database, almost 1,000. Most of them I am not using now.

Is there any way to know from the information schema tables when I have last used my tables?

2 comments

@jynus 2014-09-17 10:54:20

No, that information is not available on INFORMATION_SCHEMA (it only stores metadata).

There are several options that I can recommend you:

  • The user stats plugin from Google: after installed and enabled, it will not tell you the last time a table was accessed, but it will tell you how many times it was
  • Using the slow log: you can activate the slow log and set long_query_time = 0 (be careful of the extra overhead and disk space and then analyze per table used, so you can detect the last time it was used. Tools like pt-query-digest --review aggregate by query, not by table, but they may be useful to look for specific queries.
  • With an audit plugin you can fine-tune what you register better than with the slow log, by activating it only for certain queries or objects.
  • The performance schema can gather information about everything, but you may need MySQL 5.6 and the configuration can be a bit cumbersome

All of them will tell you information since the point you activate them, and not past information. UNIX atime is very unreliable for access time, but you may also want to give that a try, depending on the engine.

@Arun Bertil 2014-09-17 12:29:05

What I want is to check whether a table in my database is being used,need not be updated read itself is to be notified..

@Prashant Tapase 2014-09-17 10:33:59

Try this query

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;

@jynus 2014-09-17 10:39:22

That will only show the time of creation (update time is in general not reliable, you can update a table with that being NULL), and it will not show the last table read. Please check the manual: dev.mysql.com/doc/refman/5.0/en/show-table-status.html

@Prashant Tapase 2014-09-17 10:40:35

@jynus check my updated answer

@jynus 2014-09-17 10:46:21

And this is why your query is useless: pastebin.com/KSTnSbLx

@Prashant Tapase 2014-09-17 10:51:09

@jynus : not getting. It is showing datetime of last used table

@jynus 2014-09-17 10:52:33

really? I update the table and it is still telling me yesterday's time? Please read the manual link I told you. Even if it worked, that doesn't, it wouldn't show the last access time.

@Arun Bertil 2014-09-17 12:27:57

btw what i was looking for was when the contents in that table was last read

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] MySQL View from different database with the same table schema

  • 2016-09-19 08:35:54
  • herbertp.
  • 3917 View
  • 3 Score
  • 1 Answer
  • Tags:   mysql view

1 Answered Questions

Does CUBRID have Information Schema Views?

3 Answered Questions

1 Answered Questions

[SOLVED] How to locate all the tables that have a field named username

1 Answered Questions

How do I join information schema to the table's data?

1 Answered Questions

1 Answered Questions

[SOLVED] How does MySQL resolve schema information?

  • 2013-02-20 23:56:42
  • user1345414
  • 117 View
  • 2 Score
  • 1 Answer
  • Tags:   mysql windows

2 Answered Questions

Sponsored Content