The README.sql-pg in amavisd-new distribution suggests
the following SQL clauses to purge old records from a database:
DELETE FROM msgs WHERE time_iso < now() - INTERVAL '3 weeks';
DELETE FROM msgs WHERE time_iso < now() - INTERVAL '1 h' AND content IS NULL;
DELETE FROM maddr
WHERE NOT EXISTS (SELECT 1 FROM msgs WHERE sid=id)
AND NOT EXISTS (SELECT 1 FROM msgrcpt WHERE rid=id);
The 'DELETE FROM maddr' is the worst, and I spent some time
trying to optimize it. This is what I came up with, it works
faster by a factor of 1.5 to 2.
DELETE FROM maddr WHERE id IN (
SELECT id FROM maddr LEFT JOIN (
SELECT sid AS id, 1 AS f FROM msgs UNION ALL
SELECT rid AS id, 1 AS f FROM msgrcpt
) AS u USING(id) WHERE u.f IS NULL);
I'd be interested in your experience before I update
the documentation.
And I never tried it on MySQL - does it work there
and if it does, is it any faster?
Mark
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
