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 <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
