dbmail-export has this functionality already, with examples given in the man page. The caveat is that it dumps the message contents (which you can pipe to /dev/null, but the cpu time still costs you).
Aaron On Fri, Oct 26, 2007, Paul J Stevens <[EMAIL PROTECTED]> said: > Josh, > > I like it: very useful. But I thought it could use some improvements since > this > approach is not forward compatible with dbmail-2.3 because of the extensive > use > of triggers in 2.3. > > So I've stuck it in a script, made it so that postgres and sqlite users can > also > use it, and allow for more variants of trash boxes (Trash/deletedmailbox, > INBOX/Trash, etc). > > > > > > > Josh Marshall wrote: >> Hi, >> >> Every night I run the following SQL to delete old emails left in >> people's Trash folder: >> >> echo "START TRANSACTION; >> CREATE TEMPORARY TABLE dbmail_tmp_oldtrash >> SELECT message_idnr FROM dbmail_messages m >> INNER JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr >> INNER JOIN dbmail_physmessage p ON p.id=m.physmessage_id >> WHERE b.name = 'Trash' AND >> p.internal_date < date_sub(now(), interval 60 day); >> SELECT COUNT(*) AS oldtrash_messages FROM dbmail_tmp_oldtrash; >> UPDATE dbmail_messages SET deleted_flag=1,status=2 >> WHERE message_idnr IN (SELECT message_idnr FROM dbmail_tmp_oldtrash); >> COMMIT;" | mysql >> >> I got this code from a previous post on this list. >> >> The last few days this has been taking a very long time to process. Our >> mail database is around 40Gb and the average number of trash items to >> set as deleted was around 5000 mails. The above code took nearly 6 hours >> to complete, with the CPU pegged at 99% the whole time. (I noticed sieve >> scripts weren't being processed correctly during this time but no mail >> was lost.) >> >> I couldn't believe it would take that long. From what I could see it was >> the UPDATE code that took the time. I replaced the above with: >> >> echo "update dbmail_messages m, dbmail_mailboxes b, dbmail_physmessage p >> set m.deleted_flag=1,m.status=2 where m.mailbox_idnr=b.mailbox_idnr and >> p.id=m.physmessage_id and b.name = 'Trash' and p.internal_date < >> date_sub(now(), interval 60 day);" | mysql >> >> It took 0.46 seconds ! >> >> I couldn't believe it so I ran the sql again with the deleted flag and >> status to set to a different value, then set it back again. Both those >> operations took less than half a second to complete. >> >> Hope this helps someone, >> >> Regards, >> Josh. >> _______________________________________________ >> DBmail mailing list >> [email protected] >> https://mailman.fastxs.nl/mailman/listinfo/dbmail >> > > > -- > ________________________________________________________________ > Paul Stevens paul at nfg.nl > NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 > The Netherlands________________________________http://www.nfg.nl > -- _______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
