RT speed problem

RT Users
1

Hi,
I have recently moved our RT system both onto a new machine and version and
am currently experiencing massive speed problems with RT after the upgrade.
Here’s the lowdown on the before and after:

Before:
Pentium III 733
384Mb RAM
IDE ATA33 disk
RT 2.0.1

After:
Dual Pentium III 600
512Mb RAM
SCSI U2W disk (RAID-5)
RT 2.0.9

After finding the speed issues I tried moving the RT database to another
RAID-0 array on the same machine and also to another server but to no avail.
I upgraded RT to 2.0.11, also with no effect. I then tried downgrading to
2.0.5 (no effect) and subsequently 2.0.1 which fixed the issue.

Our RT database is ~350Mb. I was wondering if anyone else has seen this type
of issue and if there is a fix of any kind for it (apart from continuing to
run 2.0.1)?

Thanks.

Tim Atkinson

2

Tim> Hi, I have recently moved our RT system both onto a new machine and
Tim> version and am currently experiencing massive speed problems with RT
Tim> after the upgrade.

Tim,

RT’s database schema changed/improved a lot from 2.0.1 -> 2.0.11, in
particular somewhere around 2.0.9 the indexes/indices were tuned to improve
performance. Since it’s "very hard"™ to mess with the schema during the
upgrade process, RTs upgrades don’t touch it.

That means when you were running 2.0.11 you had, for the most part, a 2.0.1
schema with 2.0.11 functionality. It can get very slow indeed. I grew hair
waiting for some of those queries to return results :wink:

The only workable solution I found was to export the data from RT, drop the
database and recreate it from the 2.0.11 schema script, and re-import the
data. There’s several threads on this in the mailing list archives about
this. It’s a destructive procedure, so make sure you’ve got good backups, just
in case.

Good luck!

-Darren

3

“DN” == Darren Nickerson darren@dazza.org writes:

DN> The only workable solution I found was to export the data from RT, drop the
DN> database and recreate it from the 2.0.11 schema script, and re-import the
DN> data. There’s several threads on this in the mailing list archives about
DN> this. It’s a destructive procedure, so make sure you’ve got good backups, just
DN> in case.

Or, do it the easy way: diff the schema files between older and newer
release and add the necessary indexes. Only other change was the
length of three varchar fields, at least in the Postgres version. One
of the new indexes obsoletes an older one, however, since postgres
uses partial indices when the field is the first in the combined
index.

That is

CREATE INDEX Attachments1 ON Attachments (Parent);

is obsoleted by

CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);

according to the discussions on the postgres mailing lists.

I’m sure some nice foreign key relations could help out too but I’m
not gonna try to analyze that myself… :wink:

4

Vivek> Or, do it the easy way: diff the schema files between older and newer
Vivek> release and add the necessary indexes.

Easy for YOU perhaps. I started out that way, . . . but the diffs were huge
and hard to interpret because of all of other stuff that has changed which is
immaterial. I has nightmares for weeks after that.

Unless you eat database schemas for lunch, I’d vote for export, drop, recreate,
import any day!

-d

5

“DN” == Darren Nickerson darren@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
DN> were huge and hard to interpret because of all of other stuff that

The entire diff from 2.0.7 to 2.0.11 for Postgres is this. Three
fields changed size, and a bunch of new indexes added (one was
renamed, which is immeterial).

The diff to the mysql schema is pretty much identical. Just run the
CREATE INDEX commands and you’re all set.

CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId);
34c35
< Name varchar(40) NOT NULL ,
Name varchar(120) NOT NULL ,
36,37c37,38
< CorrespondAddress varchar(40) ,
< CommentAddress varchar(40) ,
CorrespondAddress varchar(120) ,
CommentAddress varchar(120) ,
137c138,139
< CREATE INDEX ACL1 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId);
CREATE INDEX ACL1 ON ACL (RightScope, PrincipalId);
CREATE INDEX ACL2 ON ACL (RightScope, RightAppliesTo, RightName, PrincipalType, PrincipalId);
205c207,209
< CREATE INDEX Users2 ON Users (EmailAddress);
CREATE INDEX Users2 ON Users (Name);
CREATE INDEX Users3 ON Users (id, EmailAddress);
CREATE INDEX Users4 ON Users (EmailAddress);
236a241

6

Vivek> Or, do it the easy way: diff the schema files between older and newer
Vivek> release and add the necessary indexes.

Easy for YOU perhaps. I started out that way, . . . but the diffs were huge
and hard to interpret because of all of other stuff that has changed which is
immaterial. I has nightmares for weeks after that.

Unless you eat database schemas for lunch, I’d vote for export, drop, recreate,
import any day!

If you are running MySQL, you can do the neat trick of shutting down
MySQL, renaming the current RT table files to effectively a different
database (eg, rt-old), starting mysql, creating your new schema and then
merrily having a script running doing SQL manipulations to put in the new
data.

Its what I plan to do if I have to upgrade the tables on a machine where
the SQL tables are larger than the current free disk on the machine.

                         Bruce Campbell                            RIPE
               Systems/Network Engineer                             NCC
             www.ripe.net - PGP562C8B1B                      Operations
7

“DN” == Darren Nickerson darren@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. :wink:

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 ,
8

“DN” == Darren Nickerson darren@dazza.org writes:

DN> Apparently you eat schemas for breakfast. :wink:

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.

9

what version of SearchBuilder are you using?
If it’s old, you should try updating it.

“Tim Atkinson” tim.atkinson@itouch.com.au writes: