Re: [tlug] How to check when a MySQL table was last SELECTED

[zev <zev@example.com>]

On Mar 9, 2011, at 9:10 PM, Josh Glover wrote:

> On 9 March 2011 11:54, Sach Jobb <sach@example.com> wrote:
> 
>> Does anyone know if there is a way to get when a table was last used
>> by a SELECT?
> 
> Check the bin logs yourself? Gross, though...

Bin log will only record the modifications, so SELECT will not be logged.

If the SELECT was slow and you had slow_query logging turned on
it would show up there.  Depending upon the version you could log all
selects by making the slow_query threshold extremely small.

You could also just sniff the traffic and run mk-query-digest on the
tcpdump traffic to find all the SELECTs from a certain time.

Also, depending upon how you allow queries to the DB it might
be in the .mysql_history log files.

I suppose you could also restrict access to the table via a stored procedure or custom code.
Then that could could log it time it had a SELECT run.
Actually, maybe MySql Proxy would do a good job of that.

In the, end this is a long way of saying I don't really know of a specific
way to do what you want easily.

Out of curiosity, what is the reason for this?

Zev