> I need to write a function which inserts a log entry in a log table and
> only
> keeps the last 30 records. I was thinking of using a subquery similar to
> the
> following:
>
> insert into log (account_id, message) values (1, 'this is a test);
> delete from log where account_id = 1 and id not in ( select id from log
> where account_id = 1 order by timestamp desc limit 30);
>
> I'm wondering if there is a more performance oriented method of doing the
> delete that I'm not thinking of.
>
Depending on whether id is a kind of auto-incremented column that never cycles, I would suggest something like:
DELETE FROM log WHERE account_id = 1 AND id < ( SELECT MIN(id) FROM log WHERE account_id = 1
ORDERBY timestamp DESC LIMIT 30);
I think there will be a performance difference with your method when the number of records to be deleted is huge.
--
Daniel