On 4/11/07, Dirk Pape <[EMAIL PROTECTED]> wrote:
Hello,
using Shredder Version 0.6 with
rtx-shredder --plugin "Tickets=limit,50;status,deleted;updated_before,`date
-I -d '1 month ago'`" --force
I added the following indexes, that improved purging deleted Tickets from 3
hours to one minute.
on Transactions:
KEY `Shredder` (`ReferenceType`,`OldReference`,`NewReference`),
KEY `Shredder2` (`Type`,`OldValue`,`NewValue`),
Above two keys are helpful, but not that effective as you think.
Shredder uses two different queries "ReferenceType = foo AND
OldReference = bar" and "ReferenceType = foo AND NewReference = bar".
Mysql uses first two columns(RefType and OldRef) of the index for the
first query, but in the second case mysql can not use the third column
(NewRef) of the index as the query has no conditions on the second
column, so mysql uses only left most part of the index. ReferenceType
column has not many possible values (~100) and left part of the index
has low originality because of this your server have to scan more
rows.
I think that it's better to split each of these indexes into two, like
suggested in shredder's docs.
KEY `Transaction Order Created` (`Created`)
on CachedGroupMembers:
KEY `Shredder` (`Via`)
may help some others if this is not already folklore.
Regards,
Dirk.
--
Dr. Dirk Pape (eAS - Projektleitung Campus Management)
Freie Universitaet Berlin
Grunewaldstr. 34a, 12165 Berlin
Tel. +49 (0)30 838 75143, Fax. +49 (0)30 838 54654
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
--
Best regards, Ruslan.
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
