How to access last access time of a row in a table

[sbaliga]

Hi, my use case requires me to check for every row when it was last accessed. By this, I mean when did a query touch this row, be it select, insert or update. (more focus on select and update though).

Is this possible? 

Using cloudsql auditing, I would only find the last access time of the table and not of any specific row in the table.

Using database triggers is not possible because in CloudSQL for MySQL, triggers are only present for DML queries.

Any inputs on how I can achieve what I want are welcome.

Thanks.

[greb]

Hi @sbaliga,

Welcome to Google Cloud Community!

You need to monitor the last accessed timestamp for each row in Cloud SQL for MySQL, specifically for SELECT and UPDATE actions. Nonetheless, Cloud SQL audit logs only monitor queries at the table level, and MySQL triggers do not allow SELECT.

Possible Solutions:

1. Application-Level Tracking (Best Approach)
• Modify UPDATE queries to also update a last_accessed timestamp column.
• For SELECT tracking, implement logging at the application layer (e.g., within an API or middleware).
2. Using Cloud SQL Query Insights
• Enables monitoring of frequently accessed queries but does not provide row-level tracking.
• Can help analyze access patterns and optimize queries.
3. Using ProxySQL for Query Logging
• ProxySQL can intercept and log queries, allowing detailed query tracking.

Considering the constraints of Cloud SQL and MySQL triggers, tracking at the application level is the best method to preserve row-level access history.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.