Nate,
Btw, versions of amavisd prior to 2.4.0 (which started suggesting
the use of a foreign key) had the following example in README.sql:
DELETE FROM msgs WHERE UNIX_TIMESTAMP()-time_num > 7*24*60*60;
DELETE FROM msgs WHERE UNIX_TIMESTAMP()-time_num > 60*60 AND content IS NULL;
DELETE quarantine FROM quarantine LEFT JOIN msgs USING(mail_id)
WHERE msgs.mail_id IS NULL;
DELETE msgrcpt FROM msgrcpt LEFT JOIN msgs USING(mail_id)
WHERE msgs.mail_id IS NULL;
DELETE FROM maddr
WHERE NOT EXISTS (SELECT sid FROM msgs WHERE sid=id)
AND NOT EXISTS (SELECT rid FROM msgrcpt WHERE rid=id);
Don't know how it compares in performance to your suggestion.
Also, you should swap the order of deletions:
> # clean maddr table from orphaned records
> DELETE LOW_PRIORITY FROM maddr WHERE NOT EXISTS (SELECT 1 FROM msgs
> WHERE sid=id) AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id)
>
> # clean msgrcpt table from orphaned records
> DELETE LOW_PRIORITY FROM msgrcpt WHERE NOT EXISTS (SELECT 1 FROM msgs
> WHERE mail_id=msgrcpt.mail_id)
The msgrcpt would better be cleaned prior to purging maddr, as it
may release a need to keep unnecessary recipient addresses around.
Mark
-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
