RTx::Shredder performance improvement (by adding new index)

In the process of investigating the performance of using Shredder to delete tickets, I found an index that seems to have a considerable improvement on performance, even above the indexes that are suggested in the documentation. I simply added:

CREATE INDEX SHREDDER_CGM3 ON CachedGroupMembers(Val, Id);

Before, when I had no indexes, deleting 500 tickets (there are ~ 100,000 in our database), the process took about 2 hours and 30 minutes. After adding the suggested indexes (which I found in the CPAN documentation), the process took about 1 hour, 15 minutes. After adding the above index, the process took about 16 minutes. I stumbled upon this while using the tool mytop (available at mytop - a top clone for MySQL).

I am pretty certain that, despite the occasionally self-referrential nature of the Val field in the CGM table, this index shouldn’t lead to any difference in results, ordering, or completeness in deletion. I haven’t had the time to check, though - so obviously I take no responsibility for any huge catastrophes that may occur on your particular database.

Anyway, thanks to Ruslan Zakirov for a great tool.

-Drew Day