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
