[rt-users] RTx-shredder with a very large database

[Jim Faulkner]

On Thu, 2 Mar 2006, Luke Vanderfluit wrote:

> This is a good approach in theory, however the drawback here is how to delete
> stuff, for example the attachements in the dump file.

I've been experimenting... and I was able to delete 350,000 rows from the 
Tickets table in 77 minutes last night.  Perhaps the "Attachment" table 
would take a lot longer for deleting so many rows, but even so it seems to 
me that the limitation here is in doing the SELECTs to figure out exactly 
what needs to be deleted, rather than in doing the deletions themselves.

In my bash script I'm trying to do SELECTs like RT would, from viewing the 
output of mysql's General log.  For example, this statement:
SELECT DISTINCT main.Id AS id FROM Attachments main , Transactions 
Transactions_1, Tickets Tickets_2  WHERE ((Tickets_2.EffectiveId = 
'$TICKETNUMBER')) AND ((Transactions_1.ObjectId = Tickets_2.id)) AND 
((Transactions_1.ObjectType = 'RT::Ticket')) AND ((main.TransactionId = 
Transactions_1.id))

seems to be a buttload faster than just:
SELECT id from Attachments where TransactionId = $TRANSACTIONID

I am by no means a SQL expert though, so maybe I'm missing some stuff by 
using the former statement rather than the latter.

Jim Faulkner