[rt-users] Performance Increase through Indexing...

[David C. Troy]

> Excellent! Thanks.
>
> I'm really kinda surprised that the Creator indices would have any effect.
> What prompted you to add those?

Well, that was sort of what I wanted your input on.  I think the ones that
made the biggest impact were the Ticket, TransactionId, and EffectiveId
indexes on the various tables;  wasn't sure what the creator field was but
assumed it was a reference to the Users table.  Any field used as an
external reference between tables is usually reasonable to index.

What would be really cool is if you could type up a moderately detailed
description of the RT2 database schema.  Then I'd know for sure what all
these fields actually do.

Dave


>
>
> On Thu, Aug 23, 2001 at 10:51:04AM -0400, David C. Troy wrote:
> >
> > Ugh -- been spending the last 24 hours optimizing my RT data tables, etc,
> > trying to clean out junk, etc.
> >
> > Was running Steve Poirier's delete_dead_tickets.pl script, and it runs
> > very slow on large databases;  not his fault.
> >
> > By creating the following indexes, I was able to increase performance of
> > RT2 and Steve's script with no other modifications anywhere.  Jesse, in
> > your opinion which of these might provide the biggest gains overall?
> >
> > alter table Transactions ADD INDEX TicketIndex(Ticket);
> > alter table Transactions ADD INDEX EffectiveTicketIndex(EffectiveTicket);
> > alter table Transactions ADD INDEX CreatorIndex(Creator);
> > alter table Attachments ADD INDEX CreatorIndex(Creator);
> > alter table Attachments add index ParentIndex(Parent);
> > alter table Attachments ADD INDEX TransactionId(TransactionId);
> > alter table Tickets ADD INDEX EffectiveIdIndex(EffectiveId);
> > alter table Tickets ADD INDEX OwnerIndex(Owner);
> > alter table Tickets ADD INDEX CreatorIndex(Creator);
> >
> > This has really helped our RT performance.
> >
> > Dave
> >
> > =====================================================================
> > David C. Troy   [dave at toad.net]                   410-544-6193 Sales
> > ToadNet - Want to go fast?                        410-544-1329 FAX
> > 570 Ritchie Highway, Severna Park, MD 21146-2925  www.toad.net
> >
> >
> > _______________________________________________
> > rt-users mailing list
> > rt-users at lists.fsck.com
> > http://lists.fsck.com/mailman/listinfo/rt-users
> >
>
> --
> http://www.bestpractical.com/products/rt  -- Trouble Ticketing. Free.
>