Why not use subselects to delete the rows like this?
DELETE FROM dbmail_messageblks WHERE EXISTS
( SELECT message_idnr FROM dbmail_messages WHERE status=3
AND message_idnr = ... );
That would need one statement per table only.
On Sun, 5 Aug 2007, Jonathan Fealy wrote:
I know it seems like a lot of queries, but it basically runs like this.
A beginning SELECT * FROM dbmail_messages WHERE status='3'; // This selects
the list of message ids to be deleted.
Now each ID is iterated over like so:
SELECT * FROM dbmail_messages WHERE message_idnr='x'; // Grab the
physmessage_id that was being used.
DELETE FROM dbmail_messages WHERE message_idnr='x';
SELECT * FROM dbmail_messages WHERE physmessage_id='y'; // Check if the
physmessage is still in use in a different message.
DELETE FROM dbmail_physmessages WHERE physmessage_id='y'; // Run this
query if no other messages use this physmessage.
DELETE FROM dbmail_messageblks WHERE physmessage_id='y'; // And this one
too if not needed anymore.
End Loop
Unfortunately not all SQL servers used by dbmail handle constraints and/or
triggers making dbmail take care of the deletes for the physmessage and
messageblk rows.
There has been some talk about how to delete orphaned physmessages, so it is
possible to do the deletes by hand some thing like this provided that you
have your database has the proper constraints or triggers to delete the other
rows in tables like dbmail_headervalue, dbmail_messageblks, etc.
dbmail-util -dy # Set delete on messages that can be dumped.
DELETE FROM dbmail_messages WHERE status='3'; // Delete the deleted messages.
DELETE FROM dbmail_physmessage WHERE id NOT IN (SELECT physmessage_id FROM
dbmail_messages); // Delete the orphans now
dbmail-util -ay # Check the whole thing
Marc Dirix wrote:
Hi,
I'm deleting about 15GB of mail, from unused mailboxes.
I've done this by setting status=3 and then running dbmail-util. Which is
now running for hours and hours.
So I started checking which statements are executed, if I'm right it is
doing:
SELECT physmessage_id
FROM dbmail_messages
WHERE message_idnr = 7714566;
DELETE FROM dbmail_messages
WHERE message_idnr = 7714566;
SELECT message_idnr
FROM dbmail_messages
WHERE physmessage_id = 4015611;
DELETE FROM dbmail_physmessage
WHERE id = 4015611;
DELETE FROM dbmail_messageblks
WHERE physmessage_id = 4015611;
Now the questions are:
1) What use has the second SELECT statement?
2) Why are you not using a transaction for this? (Or did I mis that?)
3) Is there no way to batch multiple deletes with per statement?
Marc
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
