[rt-users] Rtx::Shredder: Indexes for Performance improvements on deleting Tickets

[Ruslan Zakirov]

On 4/11/07, Dirk Pape <pape-rt at inf.fu-berlin.de> 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: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>


-- 
Best regards, Ruslan.