Re: keeping last 30 entries of a log table

On Sat, 17 Jun 2006, Daniel CAUNE wrote:

>> 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
>        ORDER BY 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.

Thanks Daniel, I'll try and benchmark them both and see if < turns out to be 
faster than NOT IN.  I guess there's no way to get around the subselect 
though.

-- 
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954