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

[Steve Poirier]

Yes its true that it is slow, but my script delete tickets one by one in
case there is a crash in the middle of the process, so a db would'ent get
messed up.

BTW, it *really* speeds up, i just altered my tables by adding the indexes
you posted. Thanks for the tip, just to delete it was at least 4 times
faster,  and also improved performance in RT WEBUI, we also maitain a rather
large db (=~ 200MB) and it was very useful.

Regards,
__
Steve Poirier
Directeur développement / Project Manager
Inet-Technologies inc.



----- Original Message -----
From: "David C. Troy" <dave at toad.net>
To: "Jesse Vincent" <jesse at bestpractical.com>
Cc: <rt-users at lists.fsck.com>
Sent: Thursday, August 23, 2001 12:21 PM
Subject: Re: [rt-users] Performance Increase through Indexing...


>
> > 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.
> >
>
>
> _______________________________________________
> rt-users mailing list
> rt-users at lists.fsck.com
> http://lists.fsck.com/mailman/listinfo/rt-users
>