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(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 --reviewaggregate by query, not by table, but they may be useful to look for specific queries.
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.
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;