Recently I had to monitor some data being output to a log table while an application was running.
The log table was quite large, and being written to constantly and also by portions of that system I wasn’t interested in.
So I only wanted to see the 10 or 20 most recent rows / records written.
There are quite a few ways to do this, but this is what I threw together. It assumes the ordering is based on an incrementing unique record id.
select count(*) into @rowcount from prodlog; select id, source_id, created_on, message from prodlog where id > (@rowcount-20) order by id;
I put the latest count of records in the table into a temporary variable. I then use that figure, minus the number of rows I want to see, to offset into the data.
Note that if the count of rows and row ids don’t match (e.g. because you used truncate) the above method will fail.
In this case you may use:
select id into @last_id from prodlog order by id desc limit 1; select id, source_id, created_on, message from prodlog where id > (@last_id-20) order by id;
This is still potential issues with this, for instance you may get less than you expected if there are gaps in the id sequence.
But it sufficed for my needs at the time 🙂