On 6/27/07, Hanne Moa <[EMAIL PROTECTED]> wrote: > On 6/27/07, Mark Martinec <[EMAIL PROTECTED]> wrote: > > CREATE TEMPORARY TABLE unused AS ( > > 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); > > > > DELETE FROM maddr WHERE id IN (SELECT * FROM unused); > > > > Perhaps you had something else in mind? > > I had this in mind, yes (well, a little simpler). This still doesn't > work very well while FOREIGN KEYS exist on msgrcpt.
I wound up dropping the FOREIGN KEYS linking msgrcpt to maddr, and the delete took seconds... Is it guaranteed that, in msgrcpt, the pair mail_id+rid will be unique? In that case we have a candidate for PRIMARY KEY. The indexes made might speed things up, I'll experiment a little. HM ------------------------------------------------------------------------- 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 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/
