TechIt

On demand “Tailing” of a database log table: select the last x records

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 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s