[rt-users] Fwd: Adding Index to several tables to improve shredder performance

[Rabin Yasharzadehe]

Hello list,

 In preparation for transferring our RT server to a new server
I started to clean up RT using the shredder tool,

For starters, I shred old tickets marked as deleted (mostly spam),

# ./rt-shredder --plugin "Tickets=query,Status = 'Deleted' AND LastUpdated
> < '30 days ago';limit,100" --sqldump /opt/shredder-restore-tickets-`date
> +%s`.sql --force


And the next step was deleting users who have no tickets

# ./rt-shredder --plugin
> "Users=no_tickets,1;status,any;replace_relations,Nobody;limit,25" --sqldump
> /opt/shredder-restore-tickets-`date +%s`.sql --force


Running the following commands from the command line would take me about
two hours to finish.

I'm no database expert, but i start searching around and enabled slow query
login for MySQL and i found out that the script hang a lot on query on the
Attachment table, the problem seemed to be a missing index for the
creator column.

mysql> ALTER TABLE  `Attachments` ADD INDEX (  `Creator` )


After adding the above indexing for this column, the deletion time dropped
to less than 5 minutes

Given the success of the above method, I used it on other tables,
and found few more places that I can add indexing to improve shredder
timing.

ALTER TABLE  `Transactions`            ADD INDEX (  `Creator`       )
> ALTER TABLE  `ObjectCustomFieldValues` ADD INDEX (  `ObjectType`    )
> ALTER TABLE  `ObjectCustomFieldValues` ADD INDEX (  `ObjectId`      )
> ALTER TABLE  `Groups`                  ADD INDEX (  `LastUpdatedBy` )
> ALTER TABLE  `Principals`              ADD INDEX (  `PrincipalType` )
> ALTER TABLE  `CachedGroupMembers`      ADD INDEX (  `Via`           )
> ALTER TABLE  `GroupMembers`            ADD INDEX (  `LastUpdatedBy` )
> ALTER TABLE  `Groups`                  ADD INDEX (  `Creator`       )
> ALTER TABLE  `Tickets`                 ADD INDEX (  `Creator`       )


My question is,
can i leave the above index ?
can those changes disturb or harm future upgrades of RT ?

Thanks in advance,
--
​​
Rabin Yasharzadehe
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20130423/57b4ded9/attachment.htm>