[rt-users] RT speed problem
Vivek Khera
khera at kcilink.com
Thu Feb 7 15:14:31 EST 2002
>>>>> "DN" == Darren Nickerson <darren at dazza.org> writes:
DN> Apparently you eat schemas for breakfast. ;-)
Apparently I do...
DN> If you'd like to distill what people should do into a few CREATE INDEX
DN> commands, I'm sure what would be VERY useful to people. Nobody should be
DN> running modern RT without these indices, but I bet many are!
Ok... Looks like some simplification is in order... (your diff is
backward, so the - lines are newer than the + lines, but that's not
too important).
DN> -d
DN> --- rt-2-0-9/etc/schema.mysql Tue Nov 6 18:04:08 2001
DN> +++ rt-2-0-0/etc/schema.mysql Thu May 31 02:57:06 2001
DN> @@ -8,10 +8,10 @@
DN> ObjectField varchar(32) NULL ,
DN> ObjectValue varchar(255) NULL ,
DN> Disabled int2 NOT NULL DEFAULT 0 ,
DN> - PRIMARY KEY (id)
DN> + PRIMARY KEY (id),
DN> + INDEX (Keyword),
DN> + INDEX (ObjectType, ObjectField, ObjectValue)
DN> );
DN> -CREATE INDEX KeywordSelects1 ON KeywordSelects (Keyword);
DN> -CREATE INDEX KeywordSelects2 ON KeywordSelects (ObjectType, ObjectField, Object
DN> Value);
DN> CREATE TABLE Attachments (
DN> id INTEGER NOT NULL AUTO_INCREMENT,
DN> TransactionId integer NOT NULL ,
Ok, the two indexes were removed from the CREATE TABLE to separate
CREATE INDEX commands. No changes necessary to your DB.
DN> @@ -21,21 +21,19 @@
DN> Filename varchar(255) NULL ,
DN> ContentType varchar(80) NULL ,
DN> ContentEncoding varchar(80) NULL ,
DN> - Content LONGTEXT NULL ,
DN> - Headers LONGTEXT NULL ,
DN> + Content LONGBLOB NULL ,
DN> + Headers LONGBLOB NULL ,
Change type from LONGBLOB to LONGTEXT so you get case insensitive
matching. May be relevent to you, mabye not.
DN> Creator integer NULL ,
DN> Created DATETIME NULL ,
DN> - PRIMARY KEY (id)
DN> + PRIMARY KEY (id),
DN> + INDEX (TransactionId)
DN> );
DN> -CREATE INDEX Attachments1 ON Attachments (Parent);
DN> -CREATE INDEX Attachments2 ON Attachments (TransactionId);
DN> -CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);
Moved index on TransactionID to separate CREATE INDEX command, other
two indexes are new. Add them to your DB, though the first one should
be unnecessary because of the third one, so I'd only add the
Attchments3 index.
DN> CREATE TABLE Queues (
DN> id INTEGER NOT NULL AUTO_INCREMENT,
DN> - Name varchar(120) NOT NULL ,
DN> + Name varchar(40) NOT NULL ,
DN> Description varchar(120) NULL ,
DN> - CorrespondAddress varchar(120) NULL ,
DN> - CommentAddress varchar(120) NULL ,
DN> + CorrespondAddress varchar(40) NULL ,
DN> + CommentAddress varchar(40) NULL ,
These fields had their max length changed from 40 to 120. Maybe
relevent to you, maybe not. some invocation of ALTER TABLE should
help for MySQL, for postgres you're gonna have to dump/reload the table.
DN> InitialPriority integer NULL ,
DN> FinalPriority integer NULL ,
DN> DefaultDueIn integer NULL ,
DN> @@ -44,9 +42,9 @@
DN> LastUpdatedBy integer NULL ,
DN> LastUpdated DATETIME NULL ,
DN> Disabled int2 NOT NULL DEFAULT 0 ,
DN> - PRIMARY KEY (id)
DN> + PRIMARY KEY (id),
DN> + UNIQUE (Name)
DN> );
DN> -CREATE UNIQUE INDEX Queues1 ON Queues (Name);
Creation of index moved to separate command. No change to DB.
DN> CREATE TABLE Links (
DN> id INTEGER NOT NULL AUTO_INCREMENT,
DN> Base varchar(240) NULL ,
DN> @@ -58,17 +56,17 @@
DN> LastUpdated DATETIME NULL ,
DN> Creator integer NULL ,
DN> Created DATETIME NULL ,
DN> - PRIMARY KEY (id)
DN> + PRIMARY KEY (id),
DN> + UNIQUE (Base, Target, Type)
DN> );
DN> -CREATE UNIQUE INDEX Links1 ON Links (Base, Target, Type);
Creation of index moved to separate command. No change to DB.
DN> CREATE TABLE Groups (
DN> id INTEGER NOT NULL AUTO_INCREMENT,
DN> Name varchar(16) NULL ,
DN> Description varchar(64) NULL ,
DN> Pseudo integer NOT NULL DEFAULT 0 ,
DN> - PRIMARY KEY (id)
DN> + PRIMARY KEY (id),
DN> + UNIQUE (Name)
DN> );
DN> -CREATE UNIQUE INDEX Groups1 ON Groups (Name);
Ditto.
DN> CREATE TABLE Watchers (
DN> id INTEGER NOT NULL AUTO_INCREMENT,
DN> Type varchar(16) NULL ,
DN> @@ -81,9 +79,9 @@
DN> Created DATETIME NULL ,
DN> LastUpdatedBy integer NULL ,
DN> LastUpdated DATETIME NULL ,
DN> - PRIMARY KEY (id)
DN> + PRIMARY KEY (id),
DN> + INDEX (Scope, Value, Type, Owner)
DN> );
DN> -CREATE INDEX Watchers1 ON Watchers (Scope, Value, Type, Owner);
Ditto.
DN> CREATE TABLE ScripConditions (
DN> id INTEGER NOT NULL AUTO_INCREMENT,
DN> Name varchar(255) NULL ,
DN> @@ -111,8 +109,6 @@
DN> Created DATETIME NULL ,
DN> PRIMARY KEY (id)
DN> );
DN> -CREATE INDEX Transactions1 ON Transactions (Ticket);
DN> -CREATE INDEX Transactions2 ON Transactions (EffectiveTicket);
Two new indexes. Add them.
DN> CREATE TABLE Scrips (
DN> id INTEGER NOT NULL AUTO_INCREMENT,
DN> ScripCondition integer NULL ,
DN> @@ -133,39 +129,38 @@
DN> RightName varchar(25) NULL ,
DN> RightScope varchar(25) NULL ,
DN> RightAppliesTo integer NULL ,
DN> - PRIMARY KEY (id)
DN> + PRIMARY KEY (id),
DN> + INDEX (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId)
DN> );
DN> -CREATE INDEX ACL1 ON ACL (RightScope, PrincipalId);
DN> -CREATE INDEX ACL2 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType,
DN> PrincipalId);
ACL1 is a new index; ACL2 is moved to separate create command. Add ACL1.
DN> CREATE TABLE GroupMembers (
DN> id INTEGER NOT NULL AUTO_INCREMENT,
DN> GroupId integer NULL ,
DN> UserId integer NULL ,
DN> - PRIMARY KEY (id)
DN> + PRIMARY KEY (id),
DN> + UNIQUE (GroupId, UserId)
DN> );
DN> -CREATE UNIQUE INDEX GroupMembers1 ON GroupMembers (GroupId, UserId);
Index moved to separate command.
DN> CREATE TABLE ObjectKeywords (
DN> id INTEGER NOT NULL AUTO_INCREMENT,
DN> Keyword integer NOT NULL ,
DN> KeywordSelect integer NOT NULL ,
DN> ObjectType varchar(32) NOT NULL ,
DN> ObjectId integer NOT NULL ,
DN> - PRIMARY KEY (id)
DN> + PRIMARY KEY (id),
DN> + UNIQUE (ObjectId, ObjectType, KeywordSelect, Keyword),
DN> + INDEX (ObjectId, ObjectType),
DN> + INDEX (Keyword)
DN> );
DN> -CREATE UNIQUE INDEX ObjectKeywords1 ON ObjectKeywords (ObjectId, ObjectType, KeywordSelect, Keyword);
DN> -CREATE INDEX ObjectKeywords2 ON ObjectKeywords (ObjectId, ObjectType);
DN> -CREATE INDEX ObjectKeywords3 ON ObjectKeywords (Keyword);
Index creation moved to separate command. No change to DB.
DN> CREATE TABLE Keywords (
DN> id INTEGER NOT NULL AUTO_INCREMENT,
DN> Name varchar(255) NOT NULL ,
DN> Description varchar(255) NULL ,
DN> Parent integer NULL ,
DN> Disabled int2 NOT NULL DEFAULT 0 ,
DN> - PRIMARY KEY (id)
DN> + PRIMARY KEY (id),
DN> + UNIQUE (Name, Parent),
DN> + INDEX (Name),
DN> + INDEX (Parent)
DN> );
DN> -CREATE UNIQUE INDEX Keywords1 ON Keywords (Name, Parent);
DN> -CREATE INDEX Keywords2 ON Keywords (Name);
DN> -CREATE INDEX Keywords3 ON Keywords (Parent);
Ditto. I suspect Keywords2 is redundant with Keywords1, but I'm not
sure since one is unique and the other is not.
DN> CREATE TABLE Users (
DN> id INTEGER NOT NULL AUTO_INCREMENT,
DN> Name varchar(120) NOT NULL ,
DN> @@ -201,12 +196,10 @@
DN> LastUpdatedBy integer NULL ,
DN> LastUpdated DATETIME NULL ,
DN> Disabled int2 NOT NULL DEFAULT 0 ,
DN> - PRIMARY KEY (id)
DN> + PRIMARY KEY (id),
DN> + UNIQUE (Name),
DN> + INDEX (EmailAddress)
DN> );
DN> -CREATE UNIQUE INDEX Users1 ON Users (Name);
DN> -CREATE INDEX Users2 ON Users (Name);
DN> -CREATE INDEX Users3 ON Users (id, EmailAddress);
DN> -CREATE INDEX Users4 ON Users (EmailAddress);
Moved indexes to separate commands. Users2 index is totally redundant
with Users1 index -- just wastes space. Add Users3 to update your DB.
DN> CREATE TABLE Tickets (
DN> id INTEGER NOT NULL AUTO_INCREMENT,
DN> EffectiveId integer NULL ,
DN> @@ -232,13 +225,10 @@
DN> Creator integer NULL ,
DN> Created DATETIME NULL ,
DN> Disabled int2 NOT NULL DEFAULT 0 ,
DN> - PRIMARY KEY (id)
DN> + PRIMARY KEY (id),
DN> + INDEX (Queue, Status),
DN> + INDEX (id, Status)
DN> );
DN> -CREATE INDEX Tickets1 ON Tickets (Queue, Status);
DN> -CREATE INDEX Tickets2 ON Tickets (Owner);
DN> -CREATE INDEX Tickets3 ON Tickets (EffectiveId);
DN> -CREATE INDEX Tickets4 ON Tickets (id, Status);
DN> -CREATE INDEX Tickets5 ON Tickets (id, EffectiveId);
DN> CREATE TABLE ScripActions (
DN> id INTEGER NOT NULL AUTO_INCREMENT,
DN> Name varchar(255) NULL ,
Index commands moved to separate commands. New indexes to add:
Tickets2, Tickets3, Tickets5.
To add the necesssary indexes, just cut/paste the CREATE INDEX into
the mysql program.
More information about the rt-users
mailing list