[rt-users] RT speed problem
Darren Nickerson
darren at dazza.org
Thu Feb 7 14:56:32 EST 2002
>>>>> On Thu, 7 Feb 2002, "Vivek" == Vivek Khera wrote:
>>>>> "DN" == Darren Nickerson <darren at dazza.org> writes:
Vivek> Or, do it the easy way: diff the schema files between older and newer
Vivek> release and add the necessary indexes.
DN> Easy for YOU perhaps. I started out that way, . . . but the diffs were
DN> huge and hard to interpret because of all of other stuff that
Vivek> The entire diff from 2.0.7 to 2.0.11 for Postgres is this. Three
Vivek> fields changed size, and a bunch of new indexes added (one was
Vivek> renamed, which is immeterial).
Vivek> The diff to the mysql schema is pretty much identical. Just run the
Vivek> CREATE INDEX commands and you're all set.
Apparently you eat schemas for breakfast. ;-)
I was facing trying to understand what do do with the following diff from
2.0.1 to 2.0.9. I can't imagine trying to reproduce all of these changes by
hand . . . I'm just not a skilled MySQL DBA (or any DBA for that matter). The
drop/recreate only took me a few minutes, even if it WAS a bit scary, and I
didn't have to try to learn SQL to make that happen.
If you'd like to distill what people should do into a few CREATE INDEX
commands, I'm sure what would be VERY useful to people. Nobody should be
running modern RT without these indices, but I bet many are!
-d
--- rt-2-0-9/etc/schema.mysql Tue Nov 6 18:04:08 2001
+++ rt-2-0-0/etc/schema.mysql Thu May 31 02:57:06 2001
@@ -8,10 +8,10 @@
ObjectField varchar(32) NULL ,
ObjectValue varchar(255) NULL ,
Disabled int2 NOT NULL DEFAULT 0 ,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ INDEX (Keyword),
+ INDEX (ObjectType, ObjectField, ObjectValue)
);
-CREATE INDEX KeywordSelects1 ON KeywordSelects (Keyword);
-CREATE INDEX KeywordSelects2 ON KeywordSelects (ObjectType, ObjectField, Object
Value);
CREATE TABLE Attachments (
id INTEGER NOT NULL AUTO_INCREMENT,
TransactionId integer NOT NULL ,
@@ -21,21 +21,19 @@
Filename varchar(255) NULL ,
ContentType varchar(80) NULL ,
ContentEncoding varchar(80) NULL ,
- Content LONGTEXT NULL ,
- Headers LONGTEXT NULL ,
+ Content LONGBLOB NULL ,
+ Headers LONGBLOB NULL ,
Creator integer NULL ,
Created DATETIME NULL ,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ INDEX (TransactionId)
);
-CREATE INDEX Attachments1 ON Attachments (Parent);
-CREATE INDEX Attachments2 ON Attachments (TransactionId);
-CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);
CREATE TABLE Queues (
id INTEGER NOT NULL AUTO_INCREMENT,
- Name varchar(120) NOT NULL ,
+ Name varchar(40) NOT NULL ,
Description varchar(120) NULL ,
- CorrespondAddress varchar(120) NULL ,
- CommentAddress varchar(120) NULL ,
+ CorrespondAddress varchar(40) NULL ,
+ CommentAddress varchar(40) NULL ,
InitialPriority integer NULL ,
FinalPriority integer NULL ,
DefaultDueIn integer NULL ,
@@ -44,9 +42,9 @@
LastUpdatedBy integer NULL ,
LastUpdated DATETIME NULL ,
Disabled int2 NOT NULL DEFAULT 0 ,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ UNIQUE (Name)
);
-CREATE UNIQUE INDEX Queues1 ON Queues (Name);
CREATE TABLE Links (
id INTEGER NOT NULL AUTO_INCREMENT,
Base varchar(240) NULL ,
@@ -58,17 +56,17 @@
LastUpdated DATETIME NULL ,
Creator integer NULL ,
Created DATETIME NULL ,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ UNIQUE (Base, Target, Type)
);
-CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
CREATE TABLE Groups (
id INTEGER NOT NULL AUTO_INCREMENT,
Name varchar(16) NULL ,
Description varchar(64) NULL ,
Pseudo integer NOT NULL DEFAULT 0 ,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ UNIQUE (Name)
);
-CREATE UNIQUE INDEX Groups1 ON Groups (Name);
CREATE TABLE Watchers (
id INTEGER NOT NULL AUTO_INCREMENT,
Type varchar(16) NULL ,
@@ -81,9 +79,9 @@
Created DATETIME NULL ,
LastUpdatedBy integer NULL ,
LastUpdated DATETIME NULL ,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ INDEX (Scope, Value, Type, Owner)
);
-CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner);
CREATE TABLE ScripConditions (
id INTEGER NOT NULL AUTO_INCREMENT,
Name varchar(255) NULL ,
@@ -111,8 +109,6 @@
Created DATETIME NULL ,
PRIMARY KEY (id)
);
-CREATE INDEX Transactions1 ON Transactions (Ticket);
-CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
CREATE TABLE Scrips (
id INTEGER NOT NULL AUTO_INCREMENT,
ScripCondition integer NULL ,
@@ -133,39 +129,38 @@
RightName varchar(25) NULL ,
RightScope varchar(25) NULL ,
RightAppliesTo integer NULL ,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ INDEX (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId)
);
-CREATE INDEX ACL1 ON ACL (RightScope, PrincipalId);
-CREATE INDEX ACL2 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType,
PrincipalId);
CREATE TABLE GroupMembers (
id INTEGER NOT NULL AUTO_INCREMENT,
GroupId integer NULL ,
UserId integer NULL ,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ UNIQUE (GroupId, UserId)
);
-CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, UserId);
CREATE TABLE ObjectKeywords (
id INTEGER NOT NULL AUTO_INCREMENT,
Keyword integer NOT NULL ,
KeywordSelect integer NOT NULL ,
ObjectType varchar(32) NOT NULL ,
ObjectId integer NOT NULL ,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ UNIQUE (ObjectId, ObjectType, KeywordSelect, Keyword),
+ INDEX (ObjectId, ObjectType),
+ INDEX (Keyword)
);
-CREATE UNIQUE INDEX ObjectKeywords1 ON ObjectKeywords (ObjectId, ObjectType, Ke
ywordSelect, Keyword);
-CREATE INDEX ObjectKeywords2 ON ObjectKeywords (ObjectId, ObjectType);
-CREATE INDEX ObjectKeywords3 ON ObjectKeywords (Keyword);
CREATE TABLE Keywords (
id INTEGER NOT NULL AUTO_INCREMENT,
Name varchar(255) NOT NULL ,
Description varchar(255) NULL ,
Parent integer NULL ,
Disabled int2 NOT NULL DEFAULT 0 ,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ UNIQUE (Name, Parent),
+ INDEX (Name),
+ INDEX (Parent)
);
-CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent);
-CREATE INDEX Keywords2 ON Keywords (Name);
-CREATE INDEX Keywords3 ON Keywords (Parent);
CREATE TABLE Users (
id INTEGER NOT NULL AUTO_INCREMENT,
Name varchar(120) NOT NULL ,
@@ -201,12 +196,10 @@
LastUpdatedBy integer NULL ,
LastUpdated DATETIME NULL ,
Disabled int2 NOT NULL DEFAULT 0 ,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ UNIQUE (Name),
+ INDEX (EmailAddress)
);
-CREATE UNIQUE INDEX Users1 ON Users (Name);
-CREATE INDEX Users2 ON Users (Name);
-CREATE INDEX Users3 ON Users (id, EmailAddress);
-CREATE INDEX Users4 ON Users (EmailAddress);
CREATE TABLE Tickets (
id INTEGER NOT NULL AUTO_INCREMENT,
EffectiveId integer NULL ,
@@ -232,13 +225,10 @@
Creator integer NULL ,
Created DATETIME NULL ,
Disabled int2 NOT NULL DEFAULT 0 ,
- PRIMARY KEY (id)
+ PRIMARY KEY (id),
+ INDEX (Queue, Status),
+ INDEX (id, Status)
);
-CREATE INDEX Tickets1 ON Tickets (Queue, Status);
-CREATE INDEX Tickets2 ON Tickets (Owner);
-CREATE INDEX Tickets3 ON Tickets (EffectiveId);
-CREATE INDEX Tickets4 ON Tickets (id, Status);
-CREATE INDEX Tickets5 ON Tickets (id, EffectiveId);
CREATE TABLE ScripActions (
id INTEGER NOT NULL AUTO_INCREMENT,
Name varchar(255) NULL ,
More information about the rt-users
mailing list