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

[Jesse Vincent]

On Thu, Aug 23, 2001 at 12:21:52PM -0400, David C. Troy wrote:
> 
> > 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.

Because the load of Users isn't done a join time, but is a delayed-load,
if the data is asked for, I suspect this particular thing doesn't buy much.
But I'll try to take a look at the others and see if there's any reason 
not to add all of them to the default schema going forward.


> 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.

What of that exists is in etc/schema.pm.

> 
> 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.
> >
> 
> 

-- 
http://www.bestpractical.com/products/rt  -- Trouble Ticketing. Free.