[rt-users] MySQL performance

[Vivek Khera]

>>>>> "s" == seph  <seph at commerceflow.com> writes:

s> One of my RT installs is very slow (over 15 seconds) to bring up an
s> update ticket screen (like comment on ticket or reply to ticket). I'm
s> pretty sure it's slow because the update ticket screen does an acl
s> check for each user to construct the change owner menu.

I just noticed that an RT upgraded from an earlier 2.0.x line to the
current is lacking some primary key indexes.  This may make pulling
up a ticket based on ticket ID slow, since there is no direct index on
ticket ID.

I did the following in Postgres (just about 20 minutes ago):

ALTER TABLE KeywordSelects ADD PRIMARY KEY (id);
ALTER TABLE Attachments ADD PRIMARY KEY (id);
ALTER TABLE Queues ADD PRIMARY KEY (id);
ALTER TABLE Links ADD PRIMARY KEY (id);
ALTER TABLE Groups ADD PRIMARY KEY (id);
ALTER TABLE Watchers ADD PRIMARY KEY (id);
ALTER TABLE ScripConditions ADD PRIMARY KEY (id);
ALTER TABLE Transactions ADD PRIMARY KEY (id);
ALTER TABLE Scrips ADD PRIMARY KEY (id);
ALTER TABLE ACL ADD PRIMARY KEY (id);
ALTER TABLE GroupMembers ADD PRIMARY KEY (id);
ALTER TABLE ObjectKeywords ADD PRIMARY KEY (id);
ALTER TABLE Keywords ADD PRIMARY KEY (id);
ALTER TABLE Users ADD PRIMARY KEY (id);
ALTER TABLE Tickets ADD PRIMARY KEY (id);
ALTER TABLE ScripActions ADD PRIMARY KEY (id);
ALTER TABLE Templates ADD PRIMARY KEY (id);

I also added some referential integrity to make deleting dead tickets
and all associated data easier.  You may or may not want to do this.

ALTER TABLE Transactions ADD CONSTRAINT transfk1 FOREIGN KEY (Ticket) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Attachments ADD CONSTRAINT attachfk1 FOREIGN KEY (TransactionID) REFERENCES Transactions(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Watchers ADD CONSTRAINT watchfk1 FOREIGN KEY (Value) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE ObjectKeywords ADD CONSTRAINT objectfk1 FOREIGN KEY (ObjectId) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;