Not registered yet?
Register now! It is easy and done in 1 minute and gives you access to special discounts and much more!
By Arun Bertil
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?
No, that information is not available on INFORMATION_SCHEMA (it only stores metadata).
There are several options that I can recommend you:
long_query_time = 0
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.
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..
Try this query
SELECT * FROM
table_schema = 'mydb'
ORDER BY LastTimeTouched LIMIT 20;
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
@jynus check my updated answer
And this is why your query is useless: pastebin.com/KSTnSbLx
@jynus : not getting. It is showing datetime of last used table
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.
btw what i was looking for was when the contents in that table was last read